Re: [GENERAL] to_date() and invalid dates
Thomas Kellerer wrote:
I asked this a while back already:
select to_date('2013-02-31', '-mm-dd');
will not generate an error (unlike e.g. Oracle)
This is by design.
When I previously asked this question the answer as this is based on
Oracle's to_date():
http://postgresql.1045698.n5.nabble.com/to-char-accepting-invalid-dates-td4598597.html#a4608551
See for example
http://www.postgresql.org/message-id/20099.1350484...@sss.pgh.pa.us
that this is known and accepted behaviour.
However,
http://www.postgresql.org/message-id/22259.1114613...@sss.pgh.pa.us
so I guess that it was not intended, but since it has been that way
for long enough it would be too painful to change it.
So actually my original answer it is by design is probably wrong.
Based upon should be read as inspired by rather than
compatible with.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_upgrade fails: Mismatch of relation OID in database - 9.2.4 to 9.3.2
I've tried to upgrade a large production database from 9.2.4 to 9.3.2 using pg_upgradecluster (using pg_upgrade method) and it fails with the error below. Upgrade using dump/restore seems to work on a test system but would require a too long downtime on the production system. # pg_upgradecluster --method=upgrade 9.2 main /data/postgresql/9.3/main [...] Removing support functions from new cluster ok Copying user relation files /data/postgresql/9.2/main/base/12034/7174572 Mismatch of relation OID in database rg_www: old OID 22046, new OID 17671 Failure, exiting Error: pg_upgrade run failed [...] Any thoughts? How can this be fixed? Regards, SW. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Correct query to check streaming replication lag
Hi Ray, We are already using the following query: SELECT CASE WHEN pg_last_xlog_receive_location( ) = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS log_delay; We cannot use pg_xlog_location_diff as we use postgresql 9.1. Regards, Granthana On Fri, Jan 17, 2014 at 8:24 PM, Ray Stell ste...@vt.edu wrote: On Jan 17, 2014, at 5:07 AM, Granthana Biswas granth...@zedo.com wrote: Yes it's purely for monitoring purpose. I use the pg_controldata cmd locally and via bash/ssh shared keys and compare various values that seem interesting such as Time of latest checkpoint, Latest checkpoint location. My interest is recoverability and checkpoints seemed relevant at the time. I found a comment in the docs: http://www.postgresql.org/docs/9.2/static/functions-admin.html pg_xlog_location_diff calculates the difference in bytes between two transaction log locations. It can be used with pg_stat_replication or some functions shown in Table 9-59http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLEto get the replication lag. and The functions shown in Table 9-60http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLEprovide information about the current status of the standby. These functions may be executed both during recovery and in normal running. These look interesting wrt lag studies and seem to work on the stby: template1=# select pg_last_xlog_receive_location(); pg_last_xlog_receive_location --- 18/9E00 (1 row) template1=# select pg_last_xlog_replay_location(); pg_last_xlog_replay_location -- 18/9E00 (1 row)
Re: [GENERAL] Correct query to check streaming replication lag
Thanks a load Michael. This is really helpful. Regards, Granthana On Tue, Jan 21, 2014 at 12:19 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Tue, Jan 21, 2014 at 2:33 PM, Sameer Kumar sameer.ku...@ashnik.com wrote: We are already using the following query: SELECT CASE WHEN pg_last_xlog_receive_location( ) = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS log_delay; This is (delay) not the correct thing to monitor. We cannot use pg_xlog_location_diff as we use postgresql 9.1. You can still use the other two methods I mentioned. FYI, here is an equivalent written in plpgsql easily findable by googling a bit, making a pg_xlog_location_diff-like function usable even in 9.1 and 9.0 servers: CREATE OR REPLACE FUNCTION pg_xlog_location_diff_sql( text, text) RETURNS numeric LANGUAGE plpgsql AS $function$ DECLARE offset1 text; offset2 text; xlog1 text; xlog2 text; SQL text; diff text; BEGIN /* Extract the Offset and xlog from input in offset and xlog variables */ offset1=split_part($1,'/',2); xlog1=split_part($1,'/',1); offset2=split_part($2,'/',2); xlog2=split_part($2,'/',1); /* Prepare SQL query for calculation based on following formula (FF00 * xlog + offset) - (FF00 * xlog + offset) which gives value in hexadecimal. Since, hexadecimal calculation is cumbersome so convert into decimal and then calculate the difference */ SQL='SELECT (x'''||'FF00'||'''::bigint * x'''||xlog1||'''::bigint + x'''||offset1||'''::bigint)'||' - (x'''||'FF00'||'''::bigint * x'''||xlog2||'''::bigint + x'''||offset2||'''::bigint)'; EXECUTE SQL into diff; /* Return the value in numeric by explicit casting */ RETURN diff::numeric; END; $function$; Source: http://vibhorkumar.wordpress.com/2013/02/18/pg_xlog_location_diff-function-for-postgreqsqlppas/ -- Michael
Re: [GENERAL] Correct query to check streaming replication lag
Yes byte lag as well as it makes more sense. Regards, Granthana Regards, Granthana On Tue, Jan 21, 2014 at 11:03 AM, Sameer Kumar sameer.ku...@ashnik.comwrote: We are already using the following query: SELECT CASE WHEN pg_last_xlog_receive_location( ) = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS log_delay; This is (delay) not the correct thing to monitor. We cannot use pg_xlog_location_diff as we use postgresql 9.1. You can still use the other two methods I mentioned. Regards Sameer Ashnik Pte Ltd, Singapore
[GENERAL] Is there a way to address all elements of JSON array when creating a constraint in PosgreSQL?
Hello,
Does PostgreSQL provide any notation/method for putting a constraint on
each element of a JSON array?
An example to illustrate:
create table orders(data json);
insert into orders values ('
{
order_id: 45,
products: [
{
product_id: 1,
name: Book
},
{
product_id: 2,
name: Painting
}
]
}
');
I can easily add a constraint on the order_id field:
alter table orders add check ((data-'order_id')::integer = 1);
Now I need to do the same with product_id. I can put constraint on
idividual array items:
alter table orders add check ((data-'products'-0-'product_id')::integer
= 1);
alter table orders add check ((data-'products'-1-'product_id')::integer
= 1);
-- etc.
So what I'm looking for is some kind of wildcard operator for matching any
JSON array element:
alter table orders add check ((data-'products'-*-'product_id')::integer
= 1);
-- ^ like this
I know that this can be done by extracting products to a separate table
with a foreign key to orders. But I want to know if this is possible within
single JSON column, so I can keep that in mind when designing a database
schema.
Re: [GENERAL] pg_upgrade fails: Mismatch of relation OID in database - 9.2.4 to 9.3.2
On 01/21/2014 05:42 AM, Stefan Warten wrote: I've tried to upgrade a large production database from 9.2.4 to 9.3.2 using pg_upgradecluster (using pg_upgrade method) and it fails with the error below. Upgrade using dump/restore seems to work on a test system but would require a too long downtime on the production system. # pg_upgradecluster --method=upgrade 9.2 main /data/postgresql/9.3/main [...] Removing support functions from new cluster ok Copying user relation files /data/postgresql/9.2/main/base/12034/7174572 Mismatch of relation OID in database rg_www: old OID 22046, new OID 17671 Failure, exiting Error: pg_upgrade run failed [...] Any thoughts? How can this be fixed? Not sure, but it would help if you provided information on what distribution you are running and the version of said distribution. I ask because a quick search on pg_upgradecluster did not show the --method switch. Regards, SW. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] UDP Overflow / UDP Drops on Standby Postgres Service
Postgres General List, I am stumped trying to prevent an overflowing UDP buffer on a standby Postgres service. Any help would be most appreciated. Essentially a UDP buffer associated with the pg_standby process on my localhost interface gradually fills up once I start Postgres until it hits its maximum capacity and then proceeds to steadily drop packets. A restart of Postgres (of course) clears the buffer, but then it begins filling up again. As far as I can tell, this is not actually causing any problems. (It is only happening to the standby service, and failover data recovery shows nothing missing.) Nevertheless, I don't want any buffers to overflow. (I have also posted this question to ServerFault ( http://serverfault.com/questions/564905/udp-overflow-udp-drops-on-standby-postgres-service). That posting has even more detail than I have provided below, such as how I identified pg_standby by querying the /proc files.) ==Salient points==: a) by querying /proc information for UDP I can see non-empty buffers, and identify the pg_standby process as the culprit b) the overflow occurs even when my firewalls on both servers (iptables) are shut down c) my UDP buffers at 16MB+ seem more than big enough. I could make them larger but that would only mask the problem d) online discussions of similar problems seem to finger either older versions of Postgres or the Statistics Collector; to rule this out I have tried to turn off all statistics collection (track_activites/counts = off), but the problem continues: e) a verbose wire sniff of the UDP packet shows nothing useful f) there is not a great deal of database activity (e.g. roughly one 16MB WAL file is replicated from the primary to the secondary service every 45 minutes) g) I formerly ran Postgres 8.3.5, with an otherwise identical setup; this problem only began when I upgraded to 9.1.9 ==Background on my setup==: -- two CentOS 6.4 x86_64 bit systems (VMs), each running Postgres 9.1.9, each in a geographically separated (50 miles) datacenter -- Postgres is active on my primary server and running in standby mode on my backup: the backup Postgres service is receiving its data two ways: -- as a warm standby processing WAL files via log shipping -- on failover the current WAL file on the primary (not yet shipped) is recovered from a DRBD partition synced from the primary box -- nothing else (of consequence) runs on these boxes except Postgres Thanks, Daniel
[GENERAL] client_min_messages not suppressing messages in psql nor pgAdminIII
This is a cross-post from
http://stackoverflow.com/questions/21238209/info-output-despite-set-client-min-messages-to-warning-just-before
since I'm not getting any answers there yet.
With postgresql-9.0.15 on CentOS 6.5 (accessed via pgAdminIII 1.18.1 on
Win2003), I have a plperlu function that outputs an INFO message. I
want to suppress it during testing (using psql, which also behaves as
below), but I can't even seem to do it from a pgAdminIII query window:
SET client_min_messages TO WARNING;
select my_info_outputting_function('lalala')
I run that and look in the messages tab, and there's my INFO message.
(This may appear similar to
http://stackoverflow.com/questions/11404206/how-to-suppress-info-messages-when-running-psql-scripts
, but I don't want to disable INFO messages for my whole session, just
part of it and then set the minimum back to NOTICE.)
What am I doing wrong with the above code snippet? Does
client_min_messages not apply to pl/perlu functions?
UPDATE: upon further investigation, it seems to happen even with plpgsql
functions, not just plperlu functions:
create or replace function my_info_outputting_function() returns
void as $$
begin
raise INFO 'this should not appear...';
return;
end;
$$ language plpgsql;
SET client_min_messages TO WARNING;
select my_info_outputting_function();
I run the above snippet in a pgAdminIII query window and this should
not appear appears in the messages tab. Quoi?
Update 2: I also tried [log_min_messages][1] just in case. Same behaviour.
Any ideas?
Thanks,
Kev
[1]:
http://bytes.com/topic/postgresql/answers/423022-supressing-notice-messages-windows-cygwin-only-not-working
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade fails: Mismatch of relation OID in database - 9.2.4 to 9.3.2
Ubuntu 12.04.2 LTS with packages from apt.postgresql.org repo. pg_upgradecluster is the one from postgresql-common=151.pgdg12.4+1. On 21.01.2014 16:36, Adrian Klaver wrote: On 01/21/2014 05:42 AM, Stefan Warten wrote: I've tried to upgrade a large production database from 9.2.4 to 9.3.2 using pg_upgradecluster (using pg_upgrade method) and it fails with the error below. Upgrade using dump/restore seems to work on a test system but would require a too long downtime on the production system. # pg_upgradecluster --method=upgrade 9.2 main /data/postgresql/9.3/main [...] Removing support functions from new cluster ok Copying user relation files /data/postgresql/9.2/main/base/12034/7174572 Mismatch of relation OID in database rg_www: old OID 22046, new OID 17671 Failure, exiting Error: pg_upgrade run failed [...] Any thoughts? How can this be fixed? Not sure, but it would help if you provided information on what distribution you are running and the version of said distribution. I ask because a quick search on pg_upgradecluster did not show the --method switch. Regards, SW. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] client_min_messages not suppressing messages in psql nor pgAdminIII
On Tue, Jan 21, 2014 at 7:57 AM, Kevin Field k...@brantaero.com wrote: This is a cross-post from http://stackoverflow.com/ questions/21238209/info-output-despite-set-client-min- messages-to-warning-just-before since I'm not getting any answers there yet. With postgresql-9.0.15 on CentOS 6.5 (accessed via pgAdminIII 1.18.1 on Win2003), I have a plperlu function that outputs an INFO message. I want to suppress it during testing (using psql, which also behaves as below), but I can't even seem to do it from a pgAdminIII query window: Why not use NOTICE? INFO is supposed to be used for things the user *requested* to see (for example, by supplying the verbose option to one of the commands which take that option). The documentation could be clearer on this, but it seems to suggest that there is no way to turn off INFO to the client. Cheers, Jeff
Re: [GENERAL] client_min_messages not suppressing messages in psql nor pgAdminIII
Jeff Janes jeff.ja...@gmail.com writes: Why not use NOTICE? INFO is supposed to be used for things the user *requested* to see (for example, by supplying the verbose option to one of the commands which take that option). The documentation could be clearer on this, but it seems to suggest that there is no way to turn off INFO to the client. Yeah. Per elog.h: #define INFO17 /* Messages specifically requested by user (eg * VACUUM VERBOSE output); always sent to * client regardless of client_min_messages, * but by default not sent to server log. */ You should not be using level INFO unless you are responding to an explicit client request to get the output. If memory serves, we'd not even have invented that level except that VACUUM VERBOSE existed before we invented the elog levels, and we wanted to preserve its always-print-the-results behavior. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UDP Overflow / UDP Drops on Standby Postgres Service
Postgres Question postgresquest...@gmail.com writes: Essentially a UDP buffer associated with the pg_standby process on my localhost interface gradually fills up once I start Postgres until it hits its maximum capacity and then proceeds to steadily drop packets. A restart of Postgres (of course) clears the buffer, but then it begins filling up again. AFAICS, there is nothing at all in pg_standby that does IPC of any sort, let alone UDP transmission in particular. It just does filesystem access. Perhaps you're running on an NFS filesystem and the UDP traffic is associated with that? If so, though, any failure to collect packets would be a bug in glibc, not pg_standby. A different theory is that pg_standby is just the last surviving child of some process that opened a UDP port and never closed it. What is spawning pg_standby in your system? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade fails: Mismatch of relation OID in database - 9.2.4 to 9.3.2
On 01/21/2014 08:13 AM, Stefan Warten wrote: Ubuntu 12.04.2 LTS with packages from apt.postgresql.org repo. pg_upgradecluster is the one from postgresql-common=151.pgdg12.4+1. Alright I see --method is there, just not in the man pages I was looking at. Reading through the script I see it will not work for installations with user created tablespaces. In the low hanging fruit category, is that by any chance the case here? Should not that be the case. log into the database rg_www and: select * from pg_class where oid = 22046; This should return the relation that pg_upgrade is having a problem with. See below for details on the information returned: http://www.postgresql.org/docs/9.2/interactive/catalog-pg-class.html This might narrow down where the problem is. On 21.01.2014 16:36, Adrian Klaver wrote: On 01/21/2014 05:42 AM, Stefan Warten wrote: I've tried to upgrade a large production database from 9.2.4 to 9.3.2 using pg_upgradecluster (using pg_upgrade method) and it fails with the error below. Upgrade using dump/restore seems to work on a test system but would require a too long downtime on the production system. # pg_upgradecluster --method=upgrade 9.2 main /data/postgresql/9.3/main [...] Removing support functions from new cluster ok Copying user relation files /data/postgresql/9.2/main/base/12034/7174572 Mismatch of relation OID in database rg_www: old OID 22046, new OID 17671 Failure, exiting Error: pg_upgrade run failed [...] Any thoughts? How can this be fixed? Not sure, but it would help if you provided information on what distribution you are running and the version of said distribution. I ask because a quick search on pg_upgradecluster did not show the --method switch. Regards, SW. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there a way to address all elements of JSON array when creating a constraint in PosgreSQL?
On Tue, Jan 21, 2014 at 1:34 AM, Maciej Szopinski maciej...@gmail.comwrote:
Hello,
Does PostgreSQL provide any notation/method for putting a constraint on
each element of a JSON array?
An example to illustrate:
[...]
I know that this can be done by extracting products to a separate table
with a foreign key to orders. But I want to know if this is possible within
single JSON column, so I can keep that in mind when designing a database
schema.
If you write a short function to help, it's possible. You would need a
subquery to make this assertion, but you can't add one directly as a check
constraint:
create table orders (data JSON);
alter table orders add check (1 = ALL((select
array_agg((a-'product_id')::integer) from
json_array_elements(data-'products') as a)));
ERROR: cannot use subquery in check constraint
create function data_product_ids(JSON) returns integer[] immutable as $$
select array_agg((a-'product_id')::integer) from
json_array_elements($1-'products') as a $$ language sql ;
CREATE FUNCTION
alter table orders add check (1 = ALL(data_product_ids(data)));
ALTER TABLE
insert into orders (data) values ('{products: [{ product_id:1 }, {
product_id:2 }]}');
INSERT 0 1
insert into orders (data) values ('{products: [{ product_id:0 }, {
product_id:2 }]}');
ERROR: new row for relation orders violates check constraint
orders_data_check
DETAIL: Failing row contains ({products: [{ product_id:0 }, {
product_id:2 }]}).
Re: [GENERAL] client_min_messages not suppressing messages in psql nor pgAdminIII
Why not use NOTICE? INFO is supposed to be used for things the user *requested* to see (for example, by supplying the verbose option to one of the commands which take that option). The documentation could be clearer on this, but it seems to suggest that there is no way to turn off INFO to the client. Yeah. Per elog.h: #define INFO17 /* Messages specifically requested by user (eg * VACUUM VERBOSE output); always sent to * client regardless of client_min_messages, * but by default not sent to server log. */ You should not be using level INFO unless you are responding to an explicit client request to get the output. If memory serves, we'd not even have invented that level except that VACUUM VERBOSE existed before we invented the elog levels, and we wanted to preserve its always-print-the-results behavior. Thank you very much Jeff and Tom for the clarification! This was bugging me, and I'm glad to know the right thing to do now. :) Kev -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_config problems on PG9.3/Centos?
Hi. I'm trying to build the table_log module for Postgres 9.3, and am wondering if there is an issue with pg_config. I installed Postgres on a fresh CentOS 6.5 with the pgdg packages: yum list installed postgres* Installed Packages postgresql93.x86_64 9.3.2-1PGDG.rhel6 @pgdg93 postgresql93-contrib.x86_64 9.3.2-1PGDG.rhel6 @pgdg93 postgresql93-devel.x86_64 9.3.2-1PGDG.rhel6 @pgdg93 postgresql93-libs.x86_64 9.3.2-1PGDG.rhel6 @pgdg93 postgresql93-pltcl.x86_64 9.3.2-1PGDG.rhel6 @pgdg93 postgresql93-server.x86_649.3.2-1PGDG.rhel6 @pgdg93 I'm using this makefile, which has the path to pg_config hard-coded in it, with make USE_PGXS=1: MODULES = table_log DATA_built = table_log.sql DOCS = README.table_log ifdef USE_PGXS PGXS := $(shell /usr/pgsql-9.3/bin/pg_config --pgxs) include $(PGXS) else subdir = contrib/table_log top_builddir = ../.. include $(top_builddir)/src/Makefile.global include $(top_srcdir)/contrib/contrib-global.mk endif This works for me on a similar pg 9.2/Centos 6 setup, but fails on 9.3: table_log.c:14:22: error: postgres.h: No such file or directory table_log.c:15:18: error: fmgr.h: No such file or directory table_log.c:16:71: error: executor/spi.h: No such file or directory table_log.c:17:53: error: commands/trigger.h: No such file or directory table_log.c:18:65: error: mb/pg_wchar.h: No such file or directory table_log.c:21:23: error: miscadmin.h: No such file or directory table_log.c:22:30: error: utils/formatting.h: No such file or directory table_log.c:23:28: error: utils/builtins.h: No such file or directory table_log.c:24:29: error: utils/lsyscache.h: No such file or directory table_log.c:25:21: error: funcapi.h: No such file or directory When I look the output of pg_config, and especially compared to my 9.2 output, it seems suspiciously lacking some 9.3 paths: [root@new-agency table_log-0.4.4]# pg_config | more BINDIR = /usr/bin DOCDIR = /usr/share/doc/pgsql HTMLDIR = /usr/share/doc/pgsql INCLUDEDIR = /usr/include PKGINCLUDEDIR = /usr/include INCLUDEDIR-SERVER = /usr/include/server LIBDIR = /usr/lib PKGLIBDIR = /usr/lib LOCALEDIR = /usr/share/locale MANDIR = /usr/share/man SHAREDIR = /usr/share SYSCONFDIR = /etc/sysconfig/pgsql PGXS = /usr/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--disable-rpath' '--prefix=/usr/pgsql-9.3' '--includedir=/usr/pgsql-9.3/include' '--mandir=/ usr/pgsql-9.3/share/man' '--datadir=/usr/pgsql-9.3/share' '--with-perl' '--with-python' '--with-tcl' '--w ith-tclconfig=/usr/lib64' '--with-openssl' '--with-pam' '--with-krb5' '--with-gssapi' '--with-includes=/u sr/include' '--with-libraries=/usr/lib64' '--enable-nls' '--with-ossp-uuid' '--with-libxml' '--with-libxs lt' '--with-ldap' '--with-system-tzdata=/usr/share/zoneinfo' '--sysconfdir=/etc/sysconfig/pgsql' '--docdi r=/usr/share/doc' 'CFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --par am=ssp-buffer-size=4 -m64 -mtune=generic -I/usr/include/et' 'CPPFLAGS= -I/usr/include/et' CC = gcc CPPFLAGS = -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include CFLAGS = -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-siz e=4 -m64 -mtune=generic -I/usr/include/et -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after- statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv CFLAGS_SL = -fpic LDFLAGS = -L../../../src/common -L/usr/lib64 -Wl,--as-needed LDFLAGS_EX = LDFLAGS_SL = LIBS = -lpgport -lpgcommon -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lcrypt -ldl - lm VERSION = PostgreSQL 9.3.2 My 9.2 pg_config: [root@hosting table_log-0.4.4]# pg_config BINDIR = /usr/pgsql-9.2/bin DOCDIR = /usr/share/doc/pgsql HTMLDIR = /usr/share/doc/pgsql INCLUDEDIR = /usr/pgsql-9.2/include PKGINCLUDEDIR = /usr/pgsql-9.2/include INCLUDEDIR-SERVER = /usr/pgsql-9.2/include/server LIBDIR = /usr/pgsql-9.2/lib PKGLIBDIR = /usr/pgsql-9.2/lib LOCALEDIR = /usr/pgsql-9.2/share/locale MANDIR = /usr/pgsql-9.2/share/man SHAREDIR = /usr/pgsql-9.2/share SYSCONFDIR = /etc/sysconfig/pgsql PGXS = /usr/pgsql-9.2/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--disable-rpath' '--prefix=/usr/pgsql-9.2' '--includedir=/usr/pgsql-9.2/include' '--mandir=/usr/pgsql-9.2/share/man' '--datadir=/usr/pgsql-9.2/share' '--with-perl' '--with-python' '--with-tcl' '--with-tclconfig=/usr/lib64' '--with-openssl' '--with-pam' '--with-krb5' '--with-gssapi' '--with-includes=/usr/include' '--with-libraries=/usr/lib64' '--enable-nls' '--with-ossp-uuid' '--with-libxml' '--with-libxslt' '--with-ldap' '--with-system-tzdata=/usr/share/zoneinfo' '--sysconfdir=/etc/sysconfig/pgsql' '--docdir=/usr/share/doc'
Re: [GENERAL] pg_config problems on PG9.3/Centos?
Ken Tanzer ken.tan...@gmail.com writes: When I look the output of pg_config, and especially compared to my 9.2 output, it seems suspiciously lacking some 9.3 paths: [root@new-agency table_log-0.4.4]# pg_config | more BINDIR = /usr/bin DOCDIR = /usr/share/doc/pgsql HTMLDIR = /usr/share/doc/pgsql INCLUDEDIR = /usr/include PKGINCLUDEDIR = /usr/include INCLUDEDIR-SERVER = /usr/include/server LIBDIR = /usr/lib PKGLIBDIR = /usr/lib LOCALEDIR = /usr/share/locale MANDIR = /usr/share/man SHAREDIR = /usr/share SYSCONFDIR = /etc/sysconfig/pgsql Exactly where is root's path finding pg_config? IIRC, most of the paths shown here are actually computed relative to the location of the pg_config executable, so I could imagine getting this kind of result if you'd done something like symlinking pg_config into /usr/bin. I would've guessed that you were invoking a pg_config shipped with the regular Red Hat postgres packages, except for this: CONFIGURE = '--disable-rpath' '--prefix=/usr/pgsql-9.3' '--includedir=/usr/pgsql-9.3/include' '--mandir=/ usr/pgsql-9.3/share/man' '--datadir=/usr/pgsql-9.3/share' '--with-perl' which seems to prove that the package was built with the correct options for PGDG's file placement. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_config problems on PG9.3/Centos?
On Tue, Jan 21, 2014 at 12:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: Exactly where is root's path finding pg_config? IIRC, most of the paths shown here are actually computed relative to the location of the pg_config executable, so I could imagine getting this kind of result if you'd done something like symlinking pg_config into /usr/bin. Oddly, there was a pg_config in /usr/bin that was not a symlink and not owned by any package. I'm really puzzled as to how it got there, but I removed it, and symlinked the one from /usr/pgsql-9.3/bin. It puts out better information. It also sounds like from your comment that symlinking to /usr/bin is frowned upon. What is the better way to deal with this? So with the pg_config thing resolved, my make now get stuck on the error below. I found a BSD bug report with the same message from 9.2, although I was able to successfully compile for 9.2 previously. I'm wondering if anyone seems something obvious or simple that could be causing this or could be fixed. I know the table_log packages are kind of ancient, but they do the trick! table_log.c: In function ‘table_log’: table_log.c:134: warning: implicit declaration of function ‘RelationGetNamespace’ table_log.c:140: error: dereferencing pointer to incomplete type table_log.c: In function ‘__table_log’: table_log.c:301: error: dereferencing pointer to incomplete type table_log.c:310: error: dereferencing pointer to incomplete type table_log.c:312: error: dereferencing pointer to incomplete type table_log.c:346: error: dereferencing pointer to incomplete type table_log.c:354: error: dereferencing pointer to incomplete type table_log.c:373: error: dereferencing pointer to incomplete type table_log.c:381: error: dereferencing pointer to incomplete type table_log.c: In function ‘table_log_restore_table’: table_log.c:794: error: ‘timestamptz_out’ undeclared (first use in this function) table_log.c:794: error: (Each undeclared identifier is reported only once table_log.c:794: error: for each function it appears in.) make: *** [table_log.o] Error 1 http://lists.freebsd.org/pipermail/freebsd-ports-bugs/2013-June/257856.html I would've guessed that you were invoking a pg_config shipped with the regular Red Hat postgres packages, except for this: CONFIGURE = '--disable-rpath' '--prefix=/usr/pgsql-9.3' '--includedir=/usr/pgsql-9.3/include' '--mandir=/ usr/pgsql-9.3/share/man' '--datadir=/usr/pgsql-9.3/share' '--with-perl' which seems to prove that the package was built with the correct options for PGDG's file placement. regards, tom lane I'm _pretty_ sure I didn't even install the CentOs postgres packages. OTOH, where that /usr/bin/pg_config came from is a complete mystery! Cheers, Ken -- AGENCY Software A data system that puts you in control 100% Free Software *http://agency-software.org/ http://agency-software.org/* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing listagency-general-requ...@lists.sourceforge.net?body=subscribe to learn more about AGENCY or follow the discussion.
Re: [GENERAL] pg_config problems on PG9.3/Centos?
Ken Tanzer ken.tan...@gmail.com writes: On Tue, Jan 21, 2014 at 12:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: IIRC, most of the paths shown here are actually computed relative to the location of the pg_config executable, so I could imagine getting this kind of result if you'd done something like symlinking pg_config into /usr/bin. Oddly, there was a pg_config in /usr/bin that was not a symlink and not owned by any package. I'm really puzzled as to how it got there, but I removed it, and symlinked the one from /usr/pgsql-9.3/bin. It puts out better information. OK. It also sounds like from your comment that symlinking to /usr/bin is frowned upon. What is the better way to deal with this? I had forgotten the details, but if pg_config is giving you the right answers then it must know about following the symlink. So nevermind that worry. So with the pg_config thing resolved, my make now get stuck on the error below. I found a BSD bug report with the same message from 9.2, although I was able to successfully compile for 9.2 previously. I'm wondering if anyone seems something obvious or simple that could be causing this or could be fixed. I know the table_log packages are kind of ancient, but they do the trick! table_log.c: In function table_log: table_log.c:134: warning: implicit declaration of function RelationGetNamespace table_log.c:140: error: dereferencing pointer to incomplete type table_log.c: In function __table_log: table_log.c:301: error: dereferencing pointer to incomplete type table_log.c:310: error: dereferencing pointer to incomplete type table_log.c:312: error: dereferencing pointer to incomplete type table_log.c:346: error: dereferencing pointer to incomplete type table_log.c:354: error: dereferencing pointer to incomplete type table_log.c:373: error: dereferencing pointer to incomplete type table_log.c:381: error: dereferencing pointer to incomplete type table_log.c: In function table_log_restore_table: table_log.c:794: error: timestamptz_out undeclared (first use in this function) table_log.c:794: error: (Each undeclared identifier is reported only once table_log.c:794: error: for each function it appears in.) make: *** [table_log.o] Error 1 It looks like the code is missing some #include's. You at least need utils/rel.h for RelationGetNamespace and utils/timestamp.h for timestamptz_out. Can't tell from this what typedef is missing but it's possible adding those will fix it; if not you'll need to look at the complained-of lines and then grep the Postgres include files to see which one provides it. We occasionally add or remove header inclusions of other headers, which probably explains why this code compiled on older versions but not 9.3. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_config problems on PG9.3/Centos?
On 01/21/2014 01:18 PM, Ken Tanzer wrote: On Tue, Jan 21, 2014 at 12:32 PM, Tom Lane t...@sss.pgh.pa.us Oddly, there was a pg_config in /usr/bin that was not a symlink and not owned by any package. I'm really puzzled as to how it got there, but I removed it, and symlinked the one from /usr/pgsql-9.3/bin. It puts out better information. It also sounds like from your comment that symlinking to /usr/bin is frowned upon. What is the better way to deal with this? So with the pg_config thing resolved, my make now get stuck on the error below. I found a BSD bug report with the same message from 9.2, although I was able to successfully compile for 9.2 previously. I'm wondering if anyone seems something obvious or simple that could be causing this or could be fixed. I know the table_log packages are kind of ancient, but they do the trick! table_log.c: In function ‘table_log’: table_log.c:134: warning: implicit declaration of function ‘RelationGetNamespace’ table_log.c:140: error: dereferencing pointer to incomplete type table_log.c: In function ‘__table_log’: table_log.c:301: error: dereferencing pointer to incomplete type table_log.c:310: error: dereferencing pointer to incomplete type table_log.c:312: error: dereferencing pointer to incomplete type table_log.c:346: error: dereferencing pointer to incomplete type table_log.c:354: error: dereferencing pointer to incomplete type table_log.c:373: error: dereferencing pointer to incomplete type table_log.c:381: error: dereferencing pointer to incomplete type table_log.c: In function ‘table_log_restore_table’: table_log.c:794: error: ‘timestamptz_out’ undeclared (first use in this function) table_log.c:794: error: (Each undeclared identifier is reported only once table_log.c:794: error: for each function it appears in.) make: *** [table_log.o] Error 1 I saw a similiar thing on the -odbc list where someone was using PGXS to build something. It seemed AFAICT the build was not picking up timestamp.h from the Postgres includes. This is where timestamptz_out is found. I suggested they manually include it in the *.c file. I never heard back so I assumed that worked, though I can not be sure. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_config problems on PG9.3/Centos?
On Tue, Jan 21, 2014 at 1:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: Ken Tanzer ken.tan...@gmail.com writes: On Tue, Jan 21, 2014 at 12:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: IIRC, most of the paths shown here are actually computed relative to the location of the pg_config executable, so I could imagine getting this kind of result if you'd done something like symlinking pg_config into /usr/bin. Oddly, there was a pg_config in /usr/bin that was not a symlink and not owned by any package. I'm really puzzled as to how it got there, but I removed it, and symlinked the one from /usr/pgsql-9.3/bin. It puts out better information. OK. It also sounds like from your comment that symlinking to /usr/bin is frowned upon. What is the better way to deal with this? I had forgotten the details, but if pg_config is giving you the right answers then it must know about following the symlink. So nevermind that worry. I'm happy not to mind, but it seems like everything else just works out of the box, so I wonder why not this little piece? So with the pg_config thing resolved, my make now get stuck on the error I' below. I found a BSD bug report with the same message from 9.2, although I was able to successfully compile for 9.2 previously. I'm wondering if anyone seems something obvious or simple that could be causing this or could be fixed. I know the table_log packages are kind of ancient, but they do the trick! table_log.c: In function ‘table_log’: table_log.c:134: warning: implicit declaration of function ‘RelationGetNamespace’ table_log.c:140: error: dereferencing pointer to incomplete type table_log.c: In function ‘__table_log’: table_log.c:301: error: dereferencing pointer to incomplete type table_log.c:310: error: dereferencing pointer to incomplete type table_log.c:312: error: dereferencing pointer to incomplete type table_log.c:346: error: dereferencing pointer to incomplete type table_log.c:354: error: dereferencing pointer to incomplete type table_log.c:373: error: dereferencing pointer to incomplete type table_log.c:381: error: dereferencing pointer to incomplete type table_log.c: In function ‘table_log_restore_table’: table_log.c:794: error: ‘timestamptz_out’ undeclared (first use in this function) table_log.c:794: error: (Each undeclared identifier is reported only once table_log.c:794: error: for each function it appears in.) make: *** [table_log.o] Error 1 It looks like the code is missing some #include's. You at least need utils/rel.h for RelationGetNamespace and utils/timestamp.h for timestamptz_out. Can't tell from this what typedef is missing but it's possible adding those will fix it; if not you'll need to look at the complained-of lines and then grep the Postgres include files to see which one provides it. We occasionally add or remove header inclusions of other headers, which probably explains why this code compiled on older versions but not 9.3. regards, tom lane Adding those two includes did the trick--thanks! Ken -- AGENCY Software A data system that puts you in control 100% Free Software *http://agency-software.org/ http://agency-software.org/* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing listagency-general-requ...@lists.sourceforge.net?body=subscribe to learn more about AGENCY or follow the discussion.
[GENERAL] CREATE EXTENSION does not seem to work in 9.2
When I tried to do CREATE EXTENSION IF NOT EXISTS /usr/pgsql-9.2/share/extension/plpgsql.control with schema public; it gave me ERROR: syntax error at or near EXTENSION CREATE LANGUAGE worked just fine. I only tried CREATE EXTENSION because the manual said that CREATE LANGUAGE was deprecated. Susan
Re: [GENERAL] CREATE EXTENSION does not seem to work in 9.2
On 01/21/2014 02:51 PM, Susan Cassidy wrote: When I tried to do CREATE EXTENSION IF NOT EXISTS /usr/pgsql-9.2/share/extension/plpgsql.control with schema public; it gave me ERROR: syntax error at or near EXTENSION CREATE LANGUAGE worked just fine. I only tried CREATE EXTENSION because the manual said that CREATE LANGUAGE was deprecated. If you trying to install plpgsql it is installed by default from 9.0+ If you trying to install something else you only need the extension name not the file path, so just plpgsql in this case. Susan -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_config problems on PG9.3/Centos?
Ken Tanzer ken.tan...@gmail.com writes: On Tue, Jan 21, 2014 at 1:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: Ken Tanzer ken.tan...@gmail.com writes: It also sounds like from your comment that symlinking to /usr/bin is frowned upon. What is the better way to deal with this? I had forgotten the details, but if pg_config is giving you the right answers then it must know about following the symlink. So nevermind that worry. I'm happy not to mind, but it seems like everything else just works out of the box, so I wonder why not this little piece? Nah, it works fine. I was just jumping to a conclusion based on insufficient information. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_config problems on PG9.3/Centos?
Hi, On Tue, 2014-01-21 at 13:18 -0800, Ken Tanzer wrote: Oddly, there was a pg_config in /usr/bin that was not a symlink and not owned by any package. I'm really puzzled as to how it got there, but I removed it, and symlinked the one from /usr/pgsql-9.3/bin. It puts out better information. Please don't do it. PGDG RPMs are designed for parallel installation (like 9.2 and 9.3 on the same machine), and then the pg_config in regular $PATH might be tricky. Anyway: I know the table_log packages are kind of ancient, but they do the trick! table_log.c: In function ‘table_log’: table_log.c:134: warning: implicit declaration of function ‘RelationGetNamespace’ snip table_log is not being maintained anymore -- you can use emaj. It is already available in the same RPM repo: http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/repoview/emaj.html Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Twitter: @DevrimGunduz , @DevrimGunduzTR signature.asc Description: This is a digitally signed message part
Re: [GENERAL] pg_upgrade tablespaces
On Mon, Jan 13, 2014 at 09:23:09AM -0500, Joseph Kregloh wrote: Right. I know there were multiple issue with this upgrade, jails probably being the biggest, but a new one I had never heard is that _if_ you are placing your tablespaces in the PGDATA directory, and you are upgrading from pre-9.2, if you rename the old data directory, you also need to start the old server and update pg_tablespace.spclocation. Just to have it on the record. I did the upgrade outside of the jail to make sure. I also tested it within jails and it worked also. OK, good to know. I thought it was the jails because I had never heard of cross-jail upgrades, but the tablespace in PGDATA was the problem. I will work on a way to detect this in the coming weeks. It would affect all back branches, not just pre-9.2. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_config problems on PG9.3/Centos?
On Tue, Jan 21, 2014 at 3:16 PM, Devrim GÜNDÜZ dev...@gunduz.org wrote: Hi, On Tue, 2014-01-21 at 13:18 -0800, Ken Tanzer wrote: Oddly, there was a pg_config in /usr/bin that was not a symlink and not owned by any package. I'm really puzzled as to how it got there, but I removed it, and symlinked the one from /usr/pgsql-9.3/bin. It puts out better information. Please don't do it. PGDG RPMs are designed for parallel installation (like 9.2 and 9.3 on the same machine), and then the pg_config in regular $PATH might be tricky. It didn't seem like a great idea to me either, but what's the better alternative? Without the symlink I get lots of errors: make USE_PGXS=1 make: pg_config: Command not found make: pg_config: Command not found make: pg_config: Command not found make: pg_config: Command not found make: pg_config: Command not found make: pg_config: Command not found make: pg_config: Command not found make: pg_config: Command not found make: pg_config: Command not found make: pg_config: Command not found Anyway: I know the table_log packages are kind of ancient, but they do the trick! table_log.c: In function 'table_log': table_log.c:134: warning: implicit declaration of function 'RelationGetNamespace' snip table_log is not being maintained anymore -- you can use emaj. It is already available in the same RPM repo: http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/repoview/emaj.html I'm not opposed to newer and better, but at first glance this sounds like headache for no gain. Currently table_log is doing the trick for me (I only use it for tracking revisions, not rollbacks), and I have several organizations running with their revision history in table_log format. Is the table format by any chance the same, and/or is there an easy way to move from one to the other? Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Twitter: @DevrimGunduz , @DevrimGunduzTR Thanks, and Cheers, Ken -- AGENCY Software A data system that puts you in control 100% Free Software *http://agency-software.org/ http://agency-software.org/* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing listagency-general-requ...@lists.sourceforge.net?body=subscribe to learn more about AGENCY or follow the discussion.
Re: [GENERAL] pg_config problems on PG9.3/Centos?
Ken Tanzer ken.tan...@gmail.com writes: On Tue, Jan 21, 2014 at 3:16 PM, Devrim GÜNDÜZ dev...@gunduz.org wrote: Please don't do it. PGDG RPMs are designed for parallel installation (like 9.2 and 9.3 on the same machine), and then the pg_config in regular $PATH might be tricky. It didn't seem like a great idea to me either, but what's the better alternative? Without the symlink I get lots of errors: You should arrange for the appropriate pg_config to be in your PATH when you're using pgxs. Copying (or symlinking) pg_config into /usr/bin is a pretty horrible substitute for a temporary PATH change. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_config problems on PG9.3/Centos?
Hi, On Tue, 2014-01-21 at 18:00 -0800, Ken Tanzer wrote: It didn't seem like a great idea to me either, but what's the better alternative? Without the symlink I get lots of errors: make USE_PGXS=1 make: pg_config: Command not found Sometimes exporting PG_CONFIG does the trick. Alternatively, while building the RPMs, we add a patch to makefiles like this: http://svn.pgrpms.org/repo/rpm/redhat/9.3/ip4r/F-20/Makefile-pgxs.patch Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Twitter: @DevrimGunduz , @DevrimGunduzTR signature.asc Description: This is a digitally signed message part
