GVKun编程网logo

PostgreSQL通过一个查询增加表的顺序

25

如果您对PostgreSQL通过一个查询增加表的顺序感兴趣，那么这篇文章一定是您不可错过的。我们将详细讲解PostgreSQL通过一个查询增加表的顺序的各种细节，此外还有关于centos7下源码编译安

如果您对PostgreSQL通过一个查询增加表的顺序感兴趣，那么这篇文章一定是您不可错过的。我们将详细讲解PostgreSQL通过一个查询增加表的顺序的各种细节，此外还有关于centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教、postgresql – Postgres函数与准备好的查询中的SQL注入、postgresql – Postgre性能不随着核心数量的增加而增加、postgresql – 在Postgres 9.2上增加work_mem和shared_buffers会大大减慢查询速度的实用技巧。

本文目录一览：

PostgreSQL通过一个查询增加表的顺序

PostgreSQL通过一个查询增加表的顺序

我想将以下两个查询合并为一个：

SELECT pg_get_serial_sequence(''purchase_orders'', ''id'');SELECT setval(''purchase_orders_id_seq'', 30000);

但是，如果我将较高的SELECT放到setval的第一个参数中，则会得到：

SELECT setval(SELECT pg_get_serial_sequence(''purchase_orders'', ''id''), 30000);ERROR: syntax error at or near "SELECT"SQL state: 42601Character: 15

如何将选择的结果（“ purchase_orders_id_seq”）传递给setval？

编辑：这样做的原因是；我想将其像一个函数一样使用，用户只需要输入表的名称和将要设置顺序的数字即可。

FUNCTION set_id_sequence(TEXT table_name, INTEGER sequence);

答案1

如果要将子查询结果作为函数参数传递，则需要在其周围加上括号：

SELECT setval((SELECT pg_get_serial_sequence(''purchase_orders'', ''id'')), 30000);

但是在这种情况下，SELECT是多余的。您可以直接调用该函数：

SELECT setval(pg_get_serial_sequence(''purchase_orders'', ''id''), 30000);

centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教

centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教

1. 下载源码

$ mkdir /usr/downloads
$ wget -c http://cn2.php.net/distributions/php-5.6.20.tar.gz
$ tar -xvf php-5.6.20.tar.gz
$ mv php-5.6.20 /usr/local/src
$ cd !$ & cd php-5.6.20
登录后复制

2. 阅读安装指导

$ ls -also
$ less README
$ less INSTALL
登录后复制

3. 安装依赖包

$ yum install apr apr-util apr-devel apr-util-devel prce lynx
登录后复制

4. 安装httpd

$ wget -c http://apache.fayea.com//httpd/httpd-2.4.20.tar.gz
$ tar -xvf httpd-2.4.20.tar.gz
$ cd httpd-2.4.20
$ ./configure \
--prefix=/usr/local/programs/apache2 \
--enable-rewrite \
--enable-so \
--enable-headers \
--enable-expires \
--with-mpm=worker \
--enable-modules=most \
--enable-deflate \
--enable-module=shared
$ make
$ make install
$ cd /usr/local/programs/apache2
$ cp bin/apachectl /etc/init.d/httpd ## 复制启动脚本
$ /etc/init.d/httpd start ## 启动apache服务器,访问http://localhost/
$ egrep -v ''^[ ]*#|^$'' /usr/local/apache2/conf/httpd.conf | nl ## 查看apache服务器的配置
## 将apache加入系统服务
vi /etc/rc.d/rc.local
```
/usr/local/programs/apache2/bin/apachectl start
```
$ cat /etc/rc.local
登录后复制

4. 安装postgresql

立即学习“PHP免费学习笔记（深入）”；

$ yum install readline-devel  ## 安装readline依赖
$ cd /usr/downloads
$ wget -c https://ftp.postgresql.org/pub/source/v9.5.0/postgresql-9.5.0.tar.bz2
$ tar -xvf postgresql-9.5.0.tar.bz2
$ cd postgresql-9.5.0
$ ./configure --prefix=/usr/local/programs/postgresql
$ make
$ su
$ make install
$ /sbin/ldconfig /usr/local/programs/postgresql/lib ## 刷新下共享动态库
$ cd /usr/local/programs/postgresql
$ bin/psql --version  ## 检查运行情况
## 开始对postgresql的配置
$ vi /etc/profile.d/postgresql.sh ## 增加环境变量,不推荐直接在/etc/profile中添加,系统更新升级时会需要merge
``` 
PATH=/usr/local/programs/postgresql:$PATH
export PATH
```
$ source /etc/profile ## 更新环境变量

## 增加用户和其他文件夹
$ adduser postgres
$ passwd postgres 
$ mkdir /usr/local/programs/postgresql/logs
$ mkdir /usr/local/programs/postgresql/data
$ chown postgres /usr/local/programs/postgresql/data
$ su - postgres

## 初始化数据库
$ ./bin/initdb -D ./data
$ ./bin/createdb test
$ ./bin/psql test
## 已有数据库，可导入data文件夹后尝试root访问，假如带密码，可能需要进一步研究下
$ ./bin/postgres -D ./data >./logs/start-log-1.log 2>&1 &
$ ./bin/psql --list  ##列出数据库
## ok，安装完成

## 自定义设置，权限控制等，可以跳过，等熟悉使用后再做
## 编辑数据库配置及权限文件：
$ vi /usr/local/programs/postgresql/data/postgresql.conf   ## 数据库配置文件
$ chown postgres postgresql.conf
$ chmod 644 postgresql.conf
$ vi /usr/local/programs/postgresql/data/pg_hba.conf   ## 权限文件
$ vi /usr/local/programs/postgresql/data/pg_ident.conf

## 设置开机自启动：
$ vi /etc/rc.d/rc.local    ## 添加如下内容
```
/usr/local/programs/postgresql/bin/postgresql start
```
登录后复制

5. 安装php

## 源码已经在第一步中下载，现在开始安装:
$ yum install libxml2 libxml2-devel libpng libpng-devel libjpeg libjpeg-devel freetype freetype-devel
$ ./configure \
--prefix=/usr/local/programs/php \
--with-apxs2=/usr/local/programs/apache2/bin/apxs \
--with-zlib \
--with-gd \
--with-jpeg-dir \
--with-png-dir \
--with-freetype-dir \
--with-zlib-dir \
--enable-mbstring \
--with-pgsql=/usr/local/programs/postgresql \
--with-pdo-pgsql=/usr/local/programs/postgresql 
$ make
$ make test
> Bug #42718 (unsafe_raw filter not applied when configured as default filter) [ext/filter/tests/bug42718.phpt]  XFAIL REASON: FILTER_UNSAFE_RAW not applied when configured as default filter, even with flags
> Bug #67296 (filter_input doesn''t validate variables) [ext/filter/tests/bug49184.phpt]  XFAIL REASON: See Bug #49184
> Bug #53640 (XBM images require width to be multiple of 8) [ext/gd/tests/bug53640.phpt]  XFAIL REASON: Padding is not implemented yet
> zend multibyte (7) [ext/mbstring/tests/zend_multibyte-07.phpt]  XFAIL REASON: https://bugs.php.net/bug.php?id=66582
> zend multibyte (9) [ext/mbstring/tests/zend_multibyte-09.phpt]  XFAIL REASON: https://bugs.php.net/bug.php?id=66582
>Bug #70470 (Built-in server truncates headers spanning over TCP packets) [sapi/cli/tests/bug70470.phpt]  XFAIL REASON: bug is not fixed yet

## 查阅官方的bug，发现：
> id=66582: status : Closed. Fixed in master (PHP7)
> id=42718: status : Assigned
> id=42718: reference to id=49184, unsolved for many years
## 那就不关心了，直接装吧
$ make install
> You may want to add: /usr/local/programs/php/lib/php to your php.ini include_path

## 那就按它说的设置吧
$ cp php.ini-development /usr/local/programs/php/lib/php.ini
```
include_path = ".;/usr/local/programs/php/lib/php"

## 然后，编辑httpd的设置，确保其能正确解析php文件
```
...
LoadModule php5_module modules/libphp5.so
...
AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .php5

...
<ifmodule dir_module>
    DirectoryIndex index.html index.php
</ifmodule>     
```

## 重启httpd，测试
$ cd /usr/local/programs/apache2
$ bin/httpd -h
$ bin/httpd -k stop
$ bin/httpd -f conf/httpd.conf
## 默认设置的www页面在./htdocs/下，那就先去里面建一个测试页面吧
$ vi htdocs/index.php
```
<?php phpinfo(); ?>
```
$ curl http://localhost/index.php |grep postgresql
#ok
登录后复制

后续应该做的事

* 1. 启动时，不需要要手动指定配置文件
* 2. php初始化www目录设置
* 3. php 用户、权限管理等

'').addClass(''pre-numbering'').hide(); $(this).addClass(''has-numbering'').parent().append($numbering); for (i = 1; i '').text(i)); }; $numbering.fadeIn(1700); }); });

以上就介绍了centos 7下源码编译安装php支持PostgreSQL，包括了postgresql,centos 7方面的内容，希望对PHP教程有兴趣的朋友有所帮助。

postgresql – Postgres函数与准备好的查询中的SQL注入

postgresql – Postgres函数与准备好的查询中的SQL注入

在Postgres中,准备好的查询和用户定义的函数是否等同于防止sql注入的机制？
一种方法比另一种方法有特别的优势吗？
这取决于.

sql函数

使用LANGUAGE sql,答案通常是肯定的.

传递的参数被视为值,并且不能进行sql注入 – 只要您不从主体调用不安全的函数并传递参数.

PL / pgsql函数

对于LANGUAGE plpgsql,答案通常是肯定的.

但是,PL / pgsql允许动态sql,其中传递的参数(或部分)连接到查询字符串并使用EXECUTE执行.这可以将用户输入转换为sql代码并使sql注入成为可能.你不能从外面告诉函数体是否正确处理它.提供工具.

仅在您需要的地方使用动态sql.使用参数作为值的纯sql语句可以安全地防止sql注入,如sql函数.

对于动态sql,最好将值作为值传递：

> USING条款. Example.

在主体上无法进行sql注入.

如果在sql字符串中连接值,请使用：

> format() with format specifier %L. Example.
> quote_literal() or quote_nullable(). Example.

安全地包装单引号中的字符串,从而避免语法错误和sql注入.

应在sql字符串中作为标识符处理的进程参数：

> format() with format specifier %I. Example.
> quote_ident(). Example.
> a cast to a registered type – 表名的regclass：_tbl :: regclass. Example.

在需要时安全地用双引号括起字符串,从而避免语法错误和sql注入.

有关：

> Refactor a PL/pgSQL function to return the output of various SELECT queries
> Define table and column names as arguments in a plpgsql function?

永远不要只是从用户输入和执行构建一个字符串.这包括由用户直接传递或从系统目录中提取的标识符.在构建动态sql时,所有必须像用户输入一样对待并安全引用！

更多关于此相关答案中的性能影响：

> Function Performance

sql注入的基础知识：

> http://bobby-tables.com/

类似的注意事项适用于允许动态sql的其他服务器端语言.

postgresql – Postgre性能不随着核心数量的增加而增加

postgresql – Postgre性能不随着核心数量的增加而增加

我正在尝试postgres google-cloud-sql并加载了一个简单的学校模式 
CREATE TABLE school ( 
    id SERIAL NOT NULL PRIMARY KEY,name TEXT NOT NULL
);

CREATE TABLE class ( 
    id SERIAL NOT NULL PRIMARY KEY,name TEXT,school_id INTEGER NOT NULL REFERENCES school
);

CREATE TABLE student ( 
    id SERIAL NOT NULL PRIMARY KEY,class_id INTEGER NOT NULL REFERENCES class
);

-- ALL id and foreign keys have indexs

总共装载了1500万行,1500所学校,每所学校500班,每班200名学生.

之后创建一个简单的pgbench脚本

\setrandom sId1 1 20000000
\setrandom sId2 1 20000000
\setrandom sId3 1 20000000

select count(*) from school s 
join class c on s.id=c.school_id 
join student stu on c.id=stu.class_id where s.id=:sId1;

select count(*) from school s 
join class c on s.id=c.school_id 
join student stu on c.id=stu.class_id where s.id=:sId2;

select count(*) from school s 
join class c on s.id=c.school_id 
join student stu on c.id=stu.class_id where s.id=:sId3;

现在运行脚本

pgbench -c 90 -f ./sql.sql  -n -t 1000

2个内核,7.5 GB,90个客户端 – 

OUTPUT:
number of transactions actually processed: 90000/90000
tps = 1519.690555 (including connections establishing)
tps = 2320.408683 (excluding connections establishing

26个内核,30 GB,90个客户端 – 

number of transactions actually processed: 90000/90000
tps = 1553.721286 (including connections establishing)
tps = 2405.664795 (excluding connections establishing)

题：
为什么我们从2核到26核才有80 tps？

我在postgres irc上问了同样的问题.

社区肯定我是客户端的pgbench,他们建议在pgbench中使用-j4,每秒增加到23k.

postgresql – 在Postgres 9.2上增加work_mem和shared_buffers会大大减慢查询速度

postgresql – 在Postgres 9.2上增加work_mem和shared_buffers会大大减慢查询速度

我有一个在RHEL 6.3上运行的Postgresql 9.2实例,8核机器,16GB内存.服务器专用于此数据库.鉴于默认的 postgresql.conf在内存设置上相当保守,我认为允许Postgres使用更多内存可能是个好主意.令我惊讶的是,遵循 wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server的建议显着减慢了我运行的每个查询,但在更复杂的查询中显然更加明显.

我也试过运行pgtune,它提供了以下建议,调整了更多参数,但这没有改变任何东西.它建议使用1/4 RAM大小的shared_buffers,这似乎符合其他地方的建议(特别是PG维基).

default_statistics_target = 50
maintenance_work_mem = 960MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 11GB
work_mem = 96MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 3840MB
max_connections = 80

我尝试在更改设置后重新索引整个数据库(使用reindex数据库),但这也无济于事.我玩过shared_buffers和work_mem.逐渐从非常保守的默认值(128k / 1MB)改变它们逐渐降低性能.

我在几个查询上运行了EXPLAIN(ANALYZE,BUFFERS),罪魁祸首似乎是Hash Join明显变慢了.我不清楚为什么.

举一些具体的例子,我有以下查询.它在默认配置下运行~2100ms,在配置上运行~3300ms,缓冲区大小增加：

select count(*) from contest c
left outer join contestparticipant cp on c.id=cp.contestId
left outer join teammember tm on tm.contestparticipantid=cp.id
left outer join staffmember sm on cp.id=sm.contestparticipantid
left outer join person p on p.id=cp.personid
left outer join personinfo pi on pi.id=cp.personinfoid
where pi.lastname like '%b%' or pi.firstname like '%a%';

上述查询的EXPLAIN(ANALYZE,BUFFERS)：

>默认缓冲区：http://explain.depesz.com/s/xaHJ
>更大的缓冲区：http://explain.depesz.com/s/Plk

问题是,当我增加缓冲区大小时,为什么我观察到性能下降？机器肯定没有内存不足.如果OS中的共享内存(shmmax和shmall)的分配设置为非常大的值,那应该不是问题.我也没有在Postgres日志中收到任何错误.我在默认配置中运行autovacuum,但我不认为它与它有任何关系.所有查询都在几秒钟内在同一台机器上运行,只需更改配置(并重新启动PG).

编辑：
我刚刚发现了一个特别有趣的事实：当我在2010年中期的iMac(OSX 10.7.5)上使用Postgres 9.2.1和16GB RAM执行相同的测试时,我没有遇到减速.
特别：

set work_mem='1MB';
select ...; // running time is ~1800 ms
set work_mem='96MB';
select ...' // running time is ~1500 ms

当我在服务器上执行完全相同的查询(上面的查询)时,我得到2100毫秒,work_mem = 1MB,3200毫秒,96 MB.

Mac有SSD,因此可以理解的速度更快,但它表现出我期望的行为.

另见follow-up discussion on pgsql-performance.

首先,请记住work_mem是每个操作,所以它可以很快地过度.一般情况下,如果你没有遇到麻烦,我会单独留下work_mem,直到你需要它为止.

看看你的查询计划,有一件事让我印象深刻,看两个计划的缓冲区命中是非常不同的,甚至连续扫描都比较慢.我怀疑这个问题与预读缓存有关,并且空间较小.这意味着你是在为重用索引和反对在磁盘上读表而偏置内存.

我的理解是Postgresql在从磁盘读取之前会查找页面的缓存,因为它不知道操作系统缓存是否会包含该页面.因为页面然后停留在缓存中,并且因为缓存比OS缓存慢,所以这会更改快速查询的类型.事实上,除了work_mem问题之外,阅读计划看起来所有的查询信息都来自缓存,但这是一个关于哪个缓存的问题.

work_mem：我们可以为排序或相关的连接操作分配多少内存.这是每个操作,而不是每个语句或每个后端,因此单个复杂查询可以使用此数量的内存.目前尚不清楚你是否达到了这个限制,但值得注意并注意到.如果增加太多,则会丢失可用于读取缓存和共享缓冲区的内存.

shared_buffers：分配给实际Postgresql页面队列的内存量.现在,理想情况下,数据库的有趣集合将保留在此处和读取缓冲区中缓存的内存中.但是,这样做可确保所有后端中最常用的信息得到缓存而不会刷新到磁盘.在Linux上,这个缓存明显慢于操作系统磁盘缓存,但它保证了操作系统磁盘缓存不会对Postgresql透明.这显然是你的问题所在.

所以当我们有一个请求时,我们首先检查共享缓冲区,因为Postgresql对这个缓存有深入的了解,并查找这些页面.如果它们不存在,我们要求操作系统从文件中打开它们,如果操作系统缓存了结果,则返回缓存副本(这比共享缓冲区快,但Pg无法判断它是缓存还是磁盘和磁盘要慢得多,所以Postgresql通常不会抓住这个机会).请记住,这也会影响随机页面和顺序页面访问.因此,使用较低的shared_buffers设置可以获得更好的性能.

我的直觉是,在具有更大shared_buffer设置的高并发环境中,您可能会获得更好或至少更一致的性能.还要记住Postgresql抓住这个内存并保留它,所以如果你在系统上运行其他东西,读缓冲区将保存其他进程读取的文件.这是一个非常庞大而复杂的话题.较大的共享缓冲区设置可提供更好的性能保证,但在某些情况下可能会降低性能.

关于PostgreSQL通过一个查询增加表的顺序的介绍现已完结，谢谢您的耐心阅读，如果想了解更多关于centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教、postgresql – Postgres函数与准备好的查询中的SQL注入、postgresql – Postgre性能不随着核心数量的增加而增加、postgresql – 在Postgres 9.2上增加work_mem和shared_buffers会大大减慢查询速度的相关知识，请在本站寻找。

本文标签：

上一篇警告：join（）[function.join]：传递了无效的参数（PHP / MySQL查询）（传递给left或substring函数的长度参数无效）

下一篇SQL Datawarehousing，是否需要使用TSQL SELECT或更好的替代方法来填充我的DIMENSION的帮助？

相关文章