[HACKERS] Failed assert ((data - start) == data_size) in heaptuple.c
Hi folks, I am running a 9.0.3 Hot Standy + Streaming Replication slave which occasionally segfaults (every 1-2 days). I rebuilt Postgres with --enable-cassert and --enable-debug, switched on core dumping and waited for some results. The first crash since enabling debugging was a failed assert in heaptuple.c: TRAP: FailedAssertion(!((data - start) == data_size), File: heaptuple.c, Line: 255) 2011-04-07 04:20:20 EST LOG: server process (PID 32195) was terminated by signal 6: Aborted 2011-04-07 04:20:20 EST LOG: terminating any other active server processes For context, the only things running on this server are the slave database, and a tomcat instance. The tomcat instance is the only connection into this database, which continually runs through a series of SELECTs (100ms sleep between each run). The slave database is basically stock standard 9.0.3 config, apart from the replication setup and shared_buffers increased to 2GB. Here's the backtrace: Core was generated by `postgres: backend surecast 127.0.0.1(37155) SELECT'. Program terminated with signal 6, Aborted. #0 0x7f40a93aba75 in *__GI_raise (sig=value optimised out) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64 64 ../nptl/sysdeps/unix/sysv/linux/raise.c: No such file or directory. in ../nptl/sysdeps/unix/sysv/linux/raise.c (gdb) bt #0 0x7f40a93aba75 in *__GI_raise (sig=value optimised out) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64 #1 0x7f40a93af5c0 in *__GI_abort () at abort.c:92 #2 0x006f861d in ExceptionalCondition (conditionName=value optimised out, errorType=value optimised out, fileName=value optimised out, lineNumber=value optimised out) at assert.c:57 #3 0x00459b07 in heap_form_minimal_tuple (tupleDescriptor=0xf6bdd0, values=0x8, isnull=0xf6c680 ) at heaptuple.c:1459 #4 0x00580d12 in ExecFetchSlotMinimalTuple (slot=0xf6bb90) at execTuples.c:684 #5 0x00588d10 in ExecHashTableInsert (hashtable=0xf4c3b0, slot=0x7dc3, hashvalue=6) at nodeHash.c:697 #6 0x00589bf6 in MultiExecHash (node=value optimised out) at nodeHash.c:123 #7 0x0058a9ab in ExecHashJoin (node=0xf24008) at nodeHashjoin.c:154 #8 0x005788a8 in ExecProcNode (node=0xf24008) at execProcnode.c:427 #9 0x0058fb21 in ExecNestLoop (node=0xf8eb98) at nodeNestloop.c:120 #10 0x005788c8 in ExecProcNode (node=0xf8eb98) at execProcnode.c:419 #11 0x0057756d in ExecutePlan (queryDesc=0xf8bc30, direction=32195, count=0) at execMain.c:1187 #12 standard_ExecutorRun (queryDesc=0xf8bc30, direction=32195, count=0) at execMain.c:280 #13 0x00642e28 in PortalRunSelect (portal=0xf11f88, forward=value optimised out, count=0, dest=0xe00120) at pquery.c:952 #14 0x006442e9 in PortalRun (portal=value optimised out, count=value optimised out, isTopLevel=value optimised out, dest=value optimised out, altdest=value optimised out, completionTag=value optimised out) at pquery.c:796 #15 0x006419e3 in exec_execute_message (argc=value optimised out, argv=value optimised out, username=value optimised out) at postgres.c:2003 #16 PostgresMain (argc=value optimised out, argv=value optimised out, username=value optimised out) at postgres.c:3988 #17 0x00606a07 in BackendRun () at postmaster.c:3555 #18 BackendStartup () at postmaster.c:3242 #19 ServerLoop () at postmaster.c:1431 #20 0x0060931d in PostmasterMain (argc=14918336, argv=0xdfb160) at postmaster.c:1092 #21 0x005a9310 in main (argc=5, argv=0xdfb140) at main.c:188 Let me know if there is any additional information I can provide. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Postgresql on multi-core CPU's: is this old news?
On 04/05/2011 02:21 PM, Mischa Sandberg wrote: Came across the following in a paper from Oct 2010. Was wondering is this is old news I missed in this group. http://pdos.csail.mit.edu/papers/linux:osdi10.pdf about Linux optimization on multi-core CPU's. Only a little old; http://postgresql.1045698.n5.nabble.com/MIT-benchmarks-pgsql-multicore-up-to-48-performance-td3173545.html shows most of the obvious comments to be made about it. There is more detail explaining why the hand-waving done in the paper about increasing NUM_LOCK_PARTITIONS is not a simple improvement at http://postgresql.1045698.n5.nabble.com/Lock-partitions-td1952557.html -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books
Re: [HACKERS] too many dotted names
On 4/7/11, Robert Haas robertmh...@gmail.com wrote:
On Wed, Apr 6, 2011 at 4:23 PM, Vladimir Kokovic
vladimir.koko...@gmail.com wrote:
Hi,
Does it make sense to treat these ?
ALTER TABLE s'd.s'd.s's'd. ADD COLUMN id bigint DEFAULT
nextval('s''d.s''d.s''d.ds''');
ERROR: improper relation name (too many dotted names): s'd.s'd.s'd.ds'
SQL state: 42601
Treat them as what?
Even nextval('s''d.s''d.s''d.ds''') is correct literal,
PostgreSQL parser treats them like error.
I think that only solution is to prohibit . (double quote and dot) to
be part of schema or sequence name.
Best regards,
Vladimir Kokovic, DP senior, Belgrade, Serbia
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Failed assert ((data - start) == data_size) in heaptuple.c
Brendan Jurd dire...@gmail.com writes: TRAP: FailedAssertion(!((data - start) == data_size), File: heaptuple.c, Line: 255) [ scratches head ... ] That implies that heap_fill_tuple came to a different conclusion about a tuple's data size than the immediately preceding heap_compute_data_size. Which I would sure want to believe is impossible. Have you checked for flaky memory on this machine? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Failed assert ((data - start) == data_size) in heaptuple.c
On 7 April 2011 16:56, Tom Lane t...@sss.pgh.pa.us wrote: Brendan Jurd dire...@gmail.com writes: TRAP: FailedAssertion(!((data - start) == data_size), File: heaptuple.c, Line: 255) [ scratches head ... ] That implies that heap_fill_tuple came to a different conclusion about a tuple's data size than the immediately preceding heap_compute_data_size. Which I would sure want to believe is impossible. Have you checked for flaky memory on this machine? We are doing so now -- although the RAM is ECC and just a few months old, so flakiness seems a distant possibility. I will report back after we've given it a proper thrashing with memtest. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] too many dotted names
Vladimir Kokovic vladimir.koko...@gmail.com writes:
On 4/7/11, Robert Haas robertmh...@gmail.com wrote:
On Wed, Apr 6, 2011 at 4:23 PM, Vladimir Kokovic
vladimir.koko...@gmail.com wrote:
ALTER TABLE s'd.s'd.s's'd. ADD COLUMN id bigint DEFAULT
nextval('s''d.s''d.s''d.ds''');
ERROR: improper relation name (too many dotted names): s'd.s'd.s'd.ds'
Treat them as what?
Even nextval('s''d.s''d.s''d.ds''') is correct literal,
Really? According to whom? This works for me:
regression=# create schema s'd.s'd;
CREATE SCHEMA
regression=# create table s'd.s'd.s's'd. (f1 int);
CREATE TABLE
regression=# create sequence s'd.s'd.s's'd.s ;
CREATE SEQUENCE
regression=# ALTER TABLE s'd.s'd.s's'd. ADD COLUMN id bigint DEFAULT
nextval('s''d.s''d.s''s''d.s');
ALTER TABLE
I think you've made up some theory about how to quote funny characters
in nextval's argument, and it's a wrong theory. You have to double
single quotes because you're writing a string literal, but other than
that it should look just like a quoted identifier in SQL.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSI bug?
hi, I think I see what is going on now. We are sometimes failing to set the commitSeqNo correctly on the lock. In particular, if a lock assigned to OldCommittedSxact is marked with InvalidSerCommitNo, it will never be cleared. The attached patch corrects this: TransferPredicateLocksToNewTarget should initialize a new lock entry's commitSeqNo to that of the old one being transferred, or take the minimum commitSeqNo if it is merging two lock entries. Also, CreatePredicateLock should initialize commitSeqNo for to InvalidSerCommitSeqNo instead of to 0. (I don't think using 0 would actually affect anything, but we should be consistent.) I also added a couple of assertions I used to track this down: a lock's commitSeqNo should never be zero, and it should be InvalidSerCommitSeqNo if and only if the lock is not held by OldCommittedSxact. Takashi, does this patch fix your problem with leaked SIReadLocks? i'm currently running bf6848bc8c82e82f857d48185554bc3e6dcf1013 with this patch applied. i haven't seen the symptom yet. i'll keep it running for a while. btw, i've noticed the following message in the server log. is it normal? LOG: could not truncate directory pg_serial: apparent wraparound YAMAMOTO Takashi Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] superusers are members of all roles?
On Thu, Apr 7, 2011 at 6:49 AM, Andrew Dunstan and...@dunslane.net wrote: On 04/07/2011 12:29 AM, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: On Wed, Apr 6, 2011 at 7:54 PM, Stephen Frostsfr...@snowman.net wrote: * Andrew Dunstan (and...@dunslane.net) wrote: The surprising (to me) consequence was that every superuser was locked out of the system. I had not granted them (or anyone) the role, but nevertheless these lines took effect. As I recall, the way we allow superusers to set role to other roles is by considering the superuser to be a member of every role. Now, I agree that such an approach doesn't make sense for pg_hba consideration. See bug #5763, and subsequent emails. Short version: Tom argued it wasn't a bug; Peter and I felt that it was. The problem here is that if Andrew had had the opposite case (a positive-logic hba entry requiring membership in some group to get into a database), and that had locked out superusers, he'd be on the warpath about that too. And with a lot more reason. In such a case I could add the superusers to the role explicitly, or make the rule cover superusers as well. But as the situation is now, any rule covering a group covers superusers, whether I want it to or not. I'd rather have a choice in the matter (and it's clear I'm not alone in that). The introduction of hot standby has made this pattern more likely to occur. It happened here because we have a bunch of users that are allowed to connect to the standby but not to the master, and the rules I was trying to implement were designed to enforce that exclusion. Is the solution possibly to assign positive entries on the basis of the superuser being a member of all groups but require negative entries to explicitly specify that they apply to superuser? That would provide least surprise for the simplistic concept of superuser - a user who can do anything any other user can - and allow for superuser remote access to be restricted if desired. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows build issues
On Wed, Apr 6, 2011 at 6:47 PM, Andrew Dunstan and...@dunslane.net wrote: On 04/06/2011 01:34 PM, Dave Page wrote: On Wed, Apr 6, 2011 at 6:27 PM, Peter Eisentrautpete...@gmx.net wrote: * I have some doubts about whether the SDK is at all needed or whether it would suffice by itself. I went with Visual Studio Express 2008. The SDK is needed with 2008 Express, but not the non-express version. The SDK on it's own should be enough for command line compilation. When you install VC Express 2008 the SDK is installed with it. A separate install is not required, as it was with VCE 2005. Oh nice :-) -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC Proposal - Caching query results in pgpool-II
2011/4/7 Tatsuo Ishii is...@postgresql.org: In my understanding pqc is not designed to be working with pgpool. Thus if a user want to use both query cache and query dispatching, replication or failover etc. which are provided by pgpool, it seems it's not possible. For this purpose maybe user could *cascade* pqc and pgpool, but I'm not sure. Even if it's possible, it will bring huge performance penalty. Another point is cache invalidation. Masanori's proposal includes cache invalidation technique by looking at write queries, which is lacking in pqc in my understanding. Probably. My question wasn't necessarily hasn't this already been done in pqc, more should this perhaps build on or integrate with pgc in order not to duplicate effort. I think at the very least, any overlap should be researched and identified - because if it can integrate parts of pgc, or work with, more effort can be spent on the new parts rather than redoing something that's already been done. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] superusers are members of all roles?
On 04/07/2011 03:48 AM, Alastair Turner wrote: The problem here is that if Andrew had had the opposite case (a positive-logic hba entry requiring membership in some group to get into a database), and that had locked out superusers, he'd be on the warpath about that too. And with a lot more reason. In such a case I could add the superusers to the role explicitly, or make the rule cover superusers as well. But as the situation is now, any rule covering a group covers superusers, whether I want it to or not. I'd rather have a choice in the matter (and it's clear I'm not alone in that). The introduction of hot standby has made this pattern more likely to occur. It happened here because we have a bunch of users that are allowed to connect to the standby but not to the master, and the rules I was trying to implement were designed to enforce that exclusion. Is the solution possibly to assign positive entries on the basis of the superuser being a member of all groups but require negative entries to explicitly specify that they apply to superuser? That would provide least surprise for the simplistic concept of superuser - a user who can do anything any other user can - and allow for superuser remote access to be restricted if desired. I think that's just about guaranteed to produce massive confusion. +foo should mean one thing, regardless of the rule type. I seriously doubt that very many people who work with this daily would agree with Tom's argument about what that should be. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .ini support for .pgpass
On Thu, Apr 7, 2011 at 2:38 AM, Peter Eisentraut pete...@gmx.net wrote: #-comments seem like a fine idea. But it would have to be the user that would put the comment in there, since we can't really install a default file. What about preparing something like pgpass.sample and installing it into $PREFIX/share, like recovery.conf? I think a man page would be the best documentation method for in-the-moment reference. This would be also helpful. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] superusers are members of all roles?
* Andrew Dunstan wrote: On 04/07/2011 03:48 AM, Alastair Turner wrote: Is the solution possibly to assign positive entries on the basis of the superuser being a member of all groups but require negative entries to explicitly specify that they apply to superuser? I think that's just about guaranteed to produce massive confusion. +foo should mean one thing, regardless of the rule type. I seriously doubt that very many people who work with this daily would agree with Tom's argument about what that should be. What about adding a second group syntax that only evaluates explicit memberships? That way, everyone could pick which behavior they liked better, and Alastair's suggestion could be done that way, too: hostall *personae_non_gratae0.0.0.0/0 reject hostall +foo0.0.0.0/0 md5 If, as Josh said, few users even know about the old syntax, there should not be much potential for confusion in adding a new one. Additionally, most things that can be done with groups in pg_hba.conf can also be done using CONNECT privilege on databases. -- Christian -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Failed assert ((data - start) == data_size) in heaptuple.c
On 04/07/2011 03:07 PM, Brendan Jurd wrote: On 7 April 2011 16:56, Tom Lanet...@sss.pgh.pa.us wrote: Brendan Jurddire...@gmail.com writes: TRAP: FailedAssertion(!((data - start) == data_size), File: heaptuple.c, Line: 255) [ scratches head ... ] That implies that heap_fill_tuple came to a different conclusion about a tuple's data size than the immediately preceding heap_compute_data_size. Which I would sure want to believe is impossible. Have you checked for flaky memory on this machine? We are doing so now -- although the RAM is ECC and just a few months old, so flakiness seems a distant possibility. I will report back after we've given it a proper thrashing with memtest. Apparently bad RAM can also mean faulty CPU (bad cache, heat problems, etc). memtest86 seems ... rough ... at best when it comes to finding issues; I've had some systems run it for a day yet continuously segfault in real-world use until the RAM was re-seated or swapped out. -- Craig Ringer -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] superusers are members of all roles?
On 04/07/2011 07:33 AM, Christian Ullrich wrote: * Andrew Dunstan wrote: On 04/07/2011 03:48 AM, Alastair Turner wrote: Is the solution possibly to assign positive entries on the basis of the superuser being a member of all groups but require negative entries to explicitly specify that they apply to superuser? I think that's just about guaranteed to produce massive confusion. +foo should mean one thing, regardless of the rule type. I seriously doubt that very many people who work with this daily would agree with Tom's argument about what that should be. What about adding a second group syntax that only evaluates explicit memberships? That way, everyone could pick which behavior they liked better, and Alastair's suggestion could be done that way, too: hostall*personae_non_gratae0.0.0.0/0reject hostall+foo0.0.0.0/0md5 If, as Josh said, few users even know about the old syntax, there should not be much potential for confusion in adding a new one. I thought about that. What I'd like to know is how many people actually want and use and expect the current behaviour. If it's more than a handful (which I seriously doubt) then that's probably the way to go. Otherwise it seems more trouble than it's worth. Additionally, most things that can be done with groups in pg_hba.conf can also be done using CONNECT privilege on databases. In my case this won't work at all, since what I need is to allow the group access on a hot standby but prevent it on the master, and the CONNECT privs will be the same on both. We also don't have negative privileges analogous to reject lines. cheers aqndrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_upgrade fix for pg_largeobject_metadata
The attached, applied patch preserves
pg_largeobject_metadata.relfrozenxid in pg_upgrade.
This is needed only in 9.1 because only 9.0 had this table and no one is
upgrading from a 9.0 beta to 9.0 anymore. We basically don't backpatch
9.0 beta fixes at this point.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
new file mode 100644
index 90cb9ab..3f6e77b
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
***
*** 50,55
--- 50,56
#include catalog/pg_class.h
#include catalog/pg_default_acl.h
#include catalog/pg_largeobject.h
+ #include catalog/pg_largeobject_metadata.h
#include catalog/pg_proc.h
#include catalog/pg_trigger.h
#include catalog/pg_type.h
*** dumpDatabase(Archive *AH)
*** 1920,1927
NULL); /* Dumper Arg */
/*
! * pg_largeobject comes from the old system intact, so set its
! * relfrozenxid.
*/
if (binary_upgrade)
{
--- 1921,1928
NULL); /* Dumper Arg */
/*
! * pg_largeobject and pg_largeobject_metadata come from the old system
! * intact, so set their relfrozenxids.
*/
if (binary_upgrade)
{
*** dumpDatabase(Archive *AH)
*** 1930,1935
--- 1931,1939
PQExpBuffer loOutQry = createPQExpBuffer();
int i_relfrozenxid;
+ /*
+ * pg_largeobject
+ */
appendPQExpBuffer(loFrozenQry, SELECT relfrozenxid\n
FROM pg_catalog.pg_class\n
WHERE oid = %u;\n,
*** dumpDatabase(Archive *AH)
*** 1946,1952
i_relfrozenxid = PQfnumber(lo_res, relfrozenxid);
! appendPQExpBuffer(loOutQry, \n-- For binary upgrade, set pg_largeobject relfrozenxid.\n);
appendPQExpBuffer(loOutQry, UPDATE pg_catalog.pg_class\n
SET relfrozenxid = '%u'\n
WHERE oid = %u;\n,
--- 1950,1956
i_relfrozenxid = PQfnumber(lo_res, relfrozenxid);
! appendPQExpBuffer(loOutQry, \n-- For binary upgrade, set pg_largeobject.relfrozenxid\n);
appendPQExpBuffer(loOutQry, UPDATE pg_catalog.pg_class\n
SET relfrozenxid = '%u'\n
WHERE oid = %u;\n,
*** dumpDatabase(Archive *AH)
*** 1960,1965
--- 1964,2010
NULL, NULL);
PQclear(lo_res);
+
+ /*
+ * pg_largeobject_metadata
+ */
+ if (g_fout-remoteVersion = 9)
+ {
+ resetPQExpBuffer(loFrozenQry);
+ resetPQExpBuffer(loOutQry);
+
+ appendPQExpBuffer(loFrozenQry, SELECT relfrozenxid\n
+ FROM pg_catalog.pg_class\n
+ WHERE oid = %u;\n,
+ LargeObjectMetadataRelationId);
+
+ lo_res = PQexec(g_conn, loFrozenQry-data);
+ check_sql_result(lo_res, g_conn, loFrozenQry-data, PGRES_TUPLES_OK);
+
+ if (PQntuples(lo_res) != 1)
+ {
+ write_msg(NULL, dumpDatabase(): could not find pg_largeobject_metadata.relfrozenxid\n);
+ exit_nicely();
+ }
+
+ i_relfrozenxid = PQfnumber(lo_res, relfrozenxid);
+
+ appendPQExpBuffer(loOutQry, \n-- For binary upgrade, set pg_largeobject_metadata.relfrozenxid\n);
+ appendPQExpBuffer(loOutQry, UPDATE pg_catalog.pg_class\n
+ SET relfrozenxid = '%u'\n
+ WHERE oid = %u;\n,
+ atoi(PQgetvalue(lo_res, 0, i_relfrozenxid)),
+ LargeObjectMetadataRelationId);
+ ArchiveEntry(AH, nilCatalogId, createDumpId(),
+ pg_largeobject_metadata, NULL, NULL, ,
+ false, pg_largeobject_metadata, SECTION_PRE_DATA,
+ loOutQry-data, , NULL,
+ NULL, 0,
+ NULL, NULL);
+
+ PQclear(lo_res);
+ }
+
destroyPQExpBuffer(loFrozenQry);
destroyPQExpBuffer(loOutQry);
}
*** dumpTableSchema(Archive *fout, TableInfo
*** 12176,12182
}
}
! appendPQExpBuffer(q, \n-- For binary upgrade, set relfrozenxid.\n);
appendPQExpBuffer(q, UPDATE pg_catalog.pg_class\n
SET relfrozenxid = '%u'\n
WHERE oid = ,
--- 12221,12227
}
}
! appendPQExpBuffer(q, \n-- For binary upgrade, set relfrozenxid\n);
appendPQExpBuffer(q, UPDATE pg_catalog.pg_class\n
SET relfrozenxid = '%u'\n
WHERE oid = ,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] superusers are members of all roles?
* Tom Lane (t...@sss.pgh.pa.us) wrote: The problem here is that if Andrew had had the opposite case (a positive-logic hba entry requiring membership in some group to get into a database), and that had locked out superusers, he'd be on the warpath about that too. And with a lot more reason. I disagree about this. I don't feel that the 'superuser is a member of every role' behavior is what's really crucial here, it's that a superuser can 'set role' to any other role and can grant/revoke role memberships, and read every table, etc. The fact that we're doing that by making the superuser be a member of every role feels more like an implementation detail- one which has now bitten us because it's affecting things that it really shouldn't. The '+group' list should be derivable from pg_auth_members and not include 'implicit' roles. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [BUGS] Failed assert ((data - start) == data_size) in heaptuple.c
Excerpts from Brendan Jurd's message of jue abr 07 03:07:32 -0300 2011: Hi folks, I am running a 9.0.3 Hot Standy + Streaming Replication slave which occasionally segfaults (every 1-2 days). I rebuilt Postgres with --enable-cassert and --enable-debug, switched on core dumping and waited for some results. What's the platform, and what's the query? Are there funny datatypes involved? -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] superusers are members of all roles?
Andrew Dunstan and...@dunslane.net writes: I thought about that. What I'd like to know is how many people actually want and use and expect the current behaviour. If it's more than a handful (which I seriously doubt) then that's probably the way to go. Otherwise it seems more trouble than it's worth. Well, the point here is that is_member_of is currently considered to be a kind of privilege test, and of course superusers should automatically pass every privilege test. If you want it to not act that way in some circumstances, we need a fairly clear theory as to which circumstances it should act which way in. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSI bug?
YAMAMOTO Takashi y...@mwd.biglobe.ne.jp wrote: LOG: could not truncate directory pg_serial: apparent wraparound Did you get a warning with this text?: memory for serializable conflict tracking is nearly exhausted If not, there's some sort of cleanup bug to fix in the predicate locking's use of SLRU. It may be benign, but we won't really know until we find it. I'm investigating. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] superusers are members of all roles?
On 04/07/2011 11:01 AM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: I thought about that. What I'd like to know is how many people actually want and use and expect the current behaviour. If it's more than a handful (which I seriously doubt) then that's probably the way to go. Otherwise it seems more trouble than it's worth. Well, the point here is that is_member_of is currently considered to be a kind of privilege test, and of course superusers should automatically pass every privilege test. If you want it to not act that way in some circumstances, we need a fairly clear theory as to which circumstances it should act which way in. Personally, other things being equal I would expect things to operate similarly to Unix groups, where root can do just about anything but is only actually a member of a small number of groups: [root@emma ~]# groups root bin daemon sys adm disk wheel I bet most DBAs and SAs would expect the same. The HBA file is the most obvious context in which this actually matters, and off hand I can't think of another. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC Proposal - Caching query results in pgpool-II
2011/4/5 Masanori Yamazaki myamazak...@gmail.com: Hello I am sending my proposal about Google Summer Of Code2011. It would be nice if you could give me your opinion. Fantastic! Please submit your proposal through the GSoC website: http://www.google-melange.com/gsoc/profile/student/google/gsoc2011 -selena -- http://chesnok.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Back-patch plpgsql fix for rowtypes with dropped columns?
I'm getting some pressure at Red Hat to back-patch this fix: http://archives.postgresql.org/pgsql-committers/2009-08/msg00068.php (commit dcb2bda9b7042dbf43f876c94ebf35d951de10e9) into the RHEL 8.4.x postgresql release. Since I have to do the work anyway, it seems to me to be sensible to commit the fix into community git as well, so that it will be available to everybody in future 8.4.x releases. 9.0 has now been out long enough that there seems no real risk of introducing new bugs, which was the reason for not back-patching at the time. The original patch modified execQual.c and some executor runtime structs to make use of the new tupconvert.c file. I'm a bit inclined to reduce the footprint of the back-patch by not touching execQual.c, but just allowing there to be some duplicated code. That would avoid any risk of breaking third-party code that might be looking at struct ConvertRowtypeExprState. I don't currently have a need to fix this before 8.4, and it looks like the existing patch doesn't apply easily to 8.3 anyway. So I'm only proposing to do this in 8.4. Comments, objections? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Back-patch plpgsql fix for rowtypes with dropped columns?
On Thu, Apr 7, 2011 at 12:10 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm getting some pressure at Red Hat to back-patch this fix: http://archives.postgresql.org/pgsql-committers/2009-08/msg00068.php (commit dcb2bda9b7042dbf43f876c94ebf35d951de10e9) into the RHEL 8.4.x postgresql release. Since I have to do the work anyway, it seems to me to be sensible to commit the fix into community git as well, so that it will be available to everybody in future 8.4.x releases. 9.0 has now been out long enough that there seems no real risk of introducing new bugs, which was the reason for not back-patching at the time. The original patch modified execQual.c and some executor runtime structs to make use of the new tupconvert.c file. I'm a bit inclined to reduce the footprint of the back-patch by not touching execQual.c, but just allowing there to be some duplicated code. That would avoid any risk of breaking third-party code that might be looking at struct ConvertRowtypeExprState. I don't currently have a need to fix this before 8.4, and it looks like the existing patch doesn't apply easily to 8.3 anyway. So I'm only proposing to do this in 8.4. Comments, objections? Seems reasonable. It's clearly a bug. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Back-patch plpgsql fix for rowtypes with dropped columns?
Hi, On Thursday 07 April 2011 18:10:35 Tom Lane wrote: I don't currently have a need to fix this before 8.4, and it looks like the existing patch doesn't apply easily to 8.3 anyway. So I'm only proposing to do this in 8.4. Comments, objections? I personally look forward to that as it has caused quite a bit of work in the field working around that problem. And I et quite a bit of calls asking what to do when that problem manifests. And all answers to that question suck. Not touching execQual.c sounds sensible. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Bruce Momjian wrote: OK, thanks to RhodiumToad on IRC, I was able to determine the cause of the two reported pg_upgrade problems he saw via IRC. It seems toast tables have xids and pg_dump is not preserving the toast relfrozenxids as it should. Heap tables have preserved relfrozenxids, but if you update a heap row but don't change the toast value, and the old heap row is later removed, the toast table can have an older relfrozenxids than the heap table. The fix for this is to have pg_dump preserve toast relfrozenxids, which can be easily added and backpatched. We might want to push a 9.0.4 for this. Second, we need to find a way for people to detect and fix existing systems that have this problem, perhaps looming when the pg_class relfrozenxid passes the toast relfrozenxid, and thirdly, we need to figure out how to get this information to users. Perhaps the communication comes through the 9.0.4 release announcement. I am not sure how to interpret the lack of replies to this email. Either it is confidence, shock, or we told you so. ;-) Anyway, the attached patch fixes the problem. The fix is for pg_dump's binary upgrade mode. This would need to be backpatched back to 8.4 because pg_migrator needs this too. I have added a personal regression test to show which pg_class.relfrozenxid values are not preserved, and with this patch the only ones not preserved are toast tables used by system tables, which are not copied from the old cluster (FirstNormalObjectId = 16384). I am attaching that old/new pg_class.relfrozenxid diff as well. Any idea how to correct existing systems? Would VACUUM FREEZE of just the toast tables work? I perhaps could create a short DO block that would vacuum freeze just toast tables; it would have to be run in every database. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c new file mode 100644 index 3f6e77b..1ccdb4d *** a/src/bin/pg_dump/pg_dump.c --- b/src/bin/pg_dump/pg_dump.c *** getTables(int *numTables) *** 3812,3817 --- 3812,3819 int i_relhasrules; int i_relhasoids; int i_relfrozenxid; + int i_toastoid; + int i_toastfrozenxid; int i_relpersistence; int i_owning_tab; int i_owning_col; *** getTables(int *numTables) *** 3855,3861 (%s c.relowner) AS rolname, c.relchecks, c.relhastriggers, c.relhasindex, c.relhasrules, c.relhasoids, ! c.relfrozenxid, c.relpersistence, CASE WHEN c.reloftype 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, d.refobjid AS owning_tab, d.refobjsubid AS owning_col, --- 3857,3865 (%s c.relowner) AS rolname, c.relchecks, c.relhastriggers, c.relhasindex, c.relhasrules, c.relhasoids, ! c.relfrozenxid, tc.oid AS toid, ! tc.relfrozenxid AS tfrozenxid, ! c.relpersistence, CASE WHEN c.reloftype 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, d.refobjid AS owning_tab, d.refobjsubid AS owning_col, *** getTables(int *numTables) *** 3889,3895 (%s c.relowner) AS rolname, c.relchecks, c.relhastriggers, c.relhasindex, c.relhasrules, c.relhasoids, ! c.relfrozenxid, 'p' AS relpersistence, CASE WHEN c.reloftype 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, d.refobjid AS owning_tab, d.refobjsubid AS owning_col, --- 3893,3901 (%s c.relowner) AS rolname, c.relchecks, c.relhastriggers, c.relhasindex, c.relhasrules, c.relhasoids, ! c.relfrozenxid, tc.oid AS toid, ! tc.relfrozenxid AS tfrozenxid, ! 'p' AS relpersistence, CASE WHEN c.reloftype 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, d.refobjid AS owning_tab, d.refobjsubid AS owning_col, *** getTables(int *numTables) *** 3922,3928 (%s c.relowner) AS rolname, c.relchecks, c.relhastriggers, c.relhasindex, c.relhasrules, c.relhasoids, ! c.relfrozenxid, 'p' AS relpersistence, NULL AS reloftype, d.refobjid AS owning_tab, d.refobjsubid AS owning_col, --- 3928,3936 (%s c.relowner) AS rolname, c.relchecks, c.relhastriggers, c.relhasindex, c.relhasrules, c.relhasoids, ! c.relfrozenxid, tc.oid AS toid, ! tc.relfrozenxid AS tfrozenxid, ! 'p' AS relpersistence, NULL AS reloftype, d.refobjid AS owning_tab, d.refobjsubid AS owning_col, *** getTables(int *numTables) *** 3955,3961 (%s relowner)
[HACKERS] Fwd: [TESTERS] [PostgreSQL 9.1 alpha5] OpenBSD and Loongson
Original Message
Subject: [TESTERS] [PostgreSQL 9.1 alpha5] OpenBSD and Loongson
Date: Thu, 7 Apr 2011 16:22:46 +0200
From: postgre...@raveland.org
To: pgsql-test...@postgresql.org
Hi,
I need the following patch to make PostgreSQL happy
on Loongson with OpenBSD (http://www.openbsd.org/loongson.html).
These restrictions no longer exist on OpenBSD.
Tested with the latest alpha (9.1 alpha5) and with
the latest 9 release (9.0.3).
Regards,
$OpenBSD$
--- src/backend/port/dynloader/openbsd.c.orig Thu Apr 7 03:30:11 2011
+++ src/backend/port/dynloader/openbsd.cThu Apr 7 03:32:13 2011
@@ -57,28 +57,18 @@ BSD44_derived_dlerror(void)
void *
BSD44_derived_dlopen(const char *file, int num)
{
-#if defined(__mips__)
- snprintf(error_message, sizeof(error_message),
-dlopen (%s) not supported, file);
- return NULL;
-#else
void *vp;
if ((vp = dlopen((char *) file, num)) == NULL)
snprintf(error_message, sizeof(error_message),
dlopen (%s) failed: %s, file, dlerror());
return vp;
-#endif
}
void *
BSD44_derived_dlsym(void *handle, const char *name)
{
-#if defined(__mips__)
- snprintf(error_message, sizeof(error_message),
-dlsym (%s) failed, name);
- return NULL;
-#elif defined(__ELF__)
+#if defined(__ELF__)
return dlsym(handle, name);
#else
void *vp;
-
HOWTO Alpha/Beta Test:
http://wiki.postgresql.org/wiki/HowToBetaTest
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-testers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On 4/7/11 9:16 AM, Bruce Momjian wrote: OK, thanks to RhodiumToad on IRC, I was able to determine the cause of the two reported pg_upgrade problems he saw via IRC. BTW, just for the release notes, RhodiumToad == Andrew Gierth. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fwd: [TESTERS] [PostgreSQL 9.1 alpha5] OpenBSD and Loongson
Josh Berkus j...@agliodbs.com writes: I need the following patch to make PostgreSQL happy on Loongson with OpenBSD (http://www.openbsd.org/loongson.html). These restrictions no longer exist on OpenBSD. Tested with the latest alpha (9.1 alpha5) and with the latest 9 release (9.0.3). I'm a bit inclined to think the right fix is not exactly what is proposed here, but rather to make the openbsd (and freebsd) versions of this file look like the netbsd one, viz #if !defined(HAVE_DLOPEN) snprintf(error_message, sizeof(error_message), dlopen (%s) not supported, file); return NULL; #else Since you can't do much useful without dlopen support in any modern version of Postgres, it doesn't seem like there is much risk of breaking any working situation --- so we might as well back-patch, too. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Headcount for PL Summit, Saturday May 21, 2011 at PgCon
Hi! We need to get a headcount for the PL Summit at PgCon on Saturday, May 21, 2011. Please sign up using this form: http://chesnok.com/u/1r A wiki page has been started here: http://wiki.postgresql.org/wiki/PgCon_2011_PL_Summit We're also working on updating this page: http://wiki.postgresql.org/wiki/PL_Matrix And we started a list of other columns that we'd like to add to the PL_Matrix here: http://wiki.postgresql.org/wiki/PL_Features_Matrix * Joe Conway and others have broached the topic of coming up with a suggested list of features to implement for new PLs, so input from all PL developers would be very helpful. Thanks! -selena -- http://chesnok.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Failed assert ((data - start) == data_size) in heaptuple.c
On 8 April 2011 00:16, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Brendan Jurd's message of jue abr 07 03:07:32 -0300 2011: I am running a 9.0.3 Hot Standy + Streaming Replication slave which occasionally segfaults (every 1-2 days). I rebuilt Postgres with --enable-cassert and --enable-debug, switched on core dumping and waited for some results. What's the platform, and what's the query? Are there funny datatypes involved? Ubuntu 10.04 x64 on: HP DL380R05 1x Quad Core Xeon E5440 10GB PC 5400 DDR ECC 2x HP 146GB 15krpm SAS drives in RAID 1+0 The tomcat instance repeatedly runs a series of some 9 queries, I'm not sure which of the queries is the culprit or even whether it is the same one each time. However, they are all straightforward SELECTs. The one with the most complicated plan joins a whole six tables. I do keep the transaction open until I have executed all the SELECTs in the series, then commit and start over again with a fresh transaction. That's just to make sure all of the queries are pulling data from the same snapshot. As for datatypes, I do have one type that I have defined which is used in one of the queries. It's just an RGB colour value, defined as a composite type: CREATE DOMAIN colour_channel AS smallint CHECK (VALUE = 0 AND VALUE 256); CREATE TYPE rgb AS ( red colour_channel, green colour_channel, bluecolour_channel ); All of the user-defined functions I have written for this db are either SQL or PL/pgSQL, and all of the functions called by these queries are either STABLE or IMMUTABLE. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Process local hint bit cache
On Mon, Apr 4, 2011 at 9:25 AM, Merlin Moncure mmonc...@gmail.com wrote:
On Sun, Apr 3, 2011 at 6:40 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Sat, Apr 2, 2011 at 8:37 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Merlin Moncure mmonc...@gmail.com writes:
On Thu, Mar 31, 2011 at 5:38 PM, Merlin Moncure mmonc...@gmail.com wrote:
working on exanding the cache to # xid 1.
patch attached. this is essentially my original idea except it's
injected directly in to tqual.c as a kind of a expansion of the 'last
seen' concept. Because the cache loops are inlined and very tight (4
int compares), it's actually cheaper than jumping out of line into
clog to test this_int = that_int;
This seems like a mess. Why is the criterion for caching commit states
different from whether the hint bit can be set? And why are you
The hint bits are always set. The page is flagged dirty if and only
if you have to dip below the process local cache to get it. The
rationale for this is simple: it caps your downside because unlike
hint bit removal or BM_UNTIDY, you only have to dip into clog and pay
the i/o once per page -- so that your downside is limited to pre-cache
behavior (minus the overhead of maintaining the cache).
I might have missed part of the thrust of your question. In the
patch, the commit status is only stored if the LSN for the xid is
known safe (instead of checking it just before setting the bit as
SetHintBits does). This is indeed different, and it was done so as to
avoid having to a. repeatedly check XLogNeedsFlush or b. cache the xid
LSN as transam.c does. Perhaps this is weak sauce, so I think the
first thing to do is have another go at doing it at the transam level.
Stay tuned...
Ok, after having done some more testing, I don't think this is gonna
work without at least some changes to tqual.c. It's exactly the stuff
that was bugging you, or some variant thereof, that needs to happen
with penalizing some cases. Letting transam.c handle 100% of the
cache management has the following issues:
*) No way to signal tqual.c to not dirty page if we got a cache hit --
the i/o mitigation strategy rests on being able to do this.
*) transam.c cache implementation should also cache transaction LSN
position, which is expensive memory wise for less benefit
*) Lots of non-inline calls. Jumping out of HeapTupleSatisifiesMVCC
into non-inline function is expensive, at least on my test machine.
Since there are more cases now where you fall through thei hint bit
check, you are elevating significantly the amount of extra calls you
make out of line which eats up all the savings you get -- sometimes
more. In particular:
*) both TransactionIdIsCurrentTransactionId and
TransactionIdIsInProgress are called *before* TransactionIdDidCommit.
so, even if you are going to get a cache 'hit', you still have to pay
for jumping out to these functions and running them.
TransactionIdIsInProgress could be made inline possibly if you go put
it under transam.c cache umbrella, it still has material cost in that
case.
*) Setting commit cache if and only if your LSN is known safe is
important innovation and must be preserved -- if you are getting lots
of 'commit cache hit' tuples, it saves a lot of time not having to
constantly recheck the LSN flush status of the same transactions over
and over.
Here's the times I'm seeing on my workstation (all timings are
somewhat noisy, accurate within 10ms or so).
test:
create table v as select generate_series(1,50) v;
select count(*) from v;
stock:
run 1: 3000 ms (setting hint bits)
run 2+ 665 ms (hint bit optimized)
transam.c cache non inline
run 1+ 1000ms
transam.c cache inline (made inline stub that proxies to existing
function on cache miss)
run 1+ 800ms
tqual.c cache, checked at TransactionIdDidCommit
run 1+ 790ms (1-2 less 'if' statements)
tqual.c cache, checked at TransactionIdDidCommit, no LSN flush check
run 1+ 740ms
tqual.c cache, checked right after hint bit, no LSN flush check
run 1+ 670ms
The last case is near hint bit performance, because the check is
happening more or less at the same place, and the check itself is a
small number of int math operations (and can be optimized further
still). I haven't yet timed the degenerate case where the commit
cache is receiving tons of misses and is constantly flushing out
pages. I'm not expecting this to be bad, but need to mention this --
I have some ideas about mitigation if it's a problem.
So the question is, what is the way forwards? Follows is the commit
cache code I'm hooking into various places, with some fixes from
previous patch. The actual implementation of the cache structure I'm
not stuck on, other than it has to be completely inline (no dynahash)
and very tight. Where it gets called from is much more important.
merlin
#define COMMIT_CACHE_BUCKET_COUNT 4
#define COMMIT_CACHE_BUCKET_BLOCKSZ BLCKSZ
#define COMMIT_CACHE_ROLLUPSZ 100
#define COMMIT_CACHE_HIT_THRESHOLD 5
typedef struct
{
Re: [HACKERS] Process local hint bit cache
On Thu, Apr 7, 2011 at 1:28 PM, Merlin Moncure mmonc...@gmail.com wrote: int ByteOffset = xid / BITS_PER_BYTE; whoops, I just notice this was wrong -- the byte offset needs to be taking bucket into account. I need to clean this up some more obviously, but the issues at play remain the same merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \dt and table size
--On 28. März 2011 13:38:23 +0100 Bernd Helmle maili...@oopsware.de wrote: But I think we can just call pg_table_size() regardless in 9.0+; I believe it'll return the same results as pg_relation_size() on non-tables. Anyone see a problem with that? Hmm yeah, seems i was thinking too complicated...here is a cleaned up version of this idea. Do we consider this for 9.1 or should I add this to the CF-Next for 9.2? -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] psql linestyle unicode and client encoding
Random thought: Wouldn't it be better if there were a setting in psql that set the linestyle to unicode only if the client encoding was actually UTF8? This might become more relevant as we set the client encoding automatically in psql in 9.1. Then a setting of, say, unicode-auto would do the right thing in all cases. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On Thu, 2011-04-07 at 12:16 -0400, Bruce Momjian wrote: Bruce Momjian wrote: OK, thanks to RhodiumToad on IRC, I was able to determine the cause of the two reported pg_upgrade problems he saw via IRC. It seems toast tables have xids and pg_dump is not preserving the toast relfrozenxids as it should. Heap tables have preserved relfrozenxids, but if you update a heap row but don't change the toast value, and the old heap row is later removed, the toast table can have an older relfrozenxids than the heap table. The fix for this is to have pg_dump preserve toast relfrozenxids, which can be easily added and backpatched. We might want to push a 9.0.4 for this. Second, we need to find a way for people to detect and fix existing systems that have this problem, perhaps looming when the pg_class relfrozenxid passes the toast relfrozenxid, and thirdly, we need to figure out how to get this information to users. Perhaps the communication comes through the 9.0.4 release announcement. I am not sure how to interpret the lack of replies to this email. Either it is confidence, shock, or we told you so. ;-) Anyway, the attached patch fixes the problem. The fix is for pg_dump's binary upgrade mode. This would need to be backpatched back to 8.4 because pg_migrator needs this too. I have added a personal regression test to show which pg_class.relfrozenxid values are not preserved, and with this patch the only ones not preserved are toast tables used by system tables, which are not copied from the old cluster (FirstNormalObjectId = 16384). I am attaching that old/new pg_class.relfrozenxid diff as well. Any idea how to correct existing systems? Would VACUUM FREEZE of just the toast tables work? VACUUM FREEZE will never set the relfrozenxid backward. If it was never preserved to begin with, I assume that the existing value could be arbitrarily before or after, so it might not be updated. I think that after you VACUUM FREEZE the toast table, then the real oldest frozen xid (as opposed to the bad value in relfrozenxid for the toast table) would have to be the same or newer than that of the heap. Right? That means you could safely copy the heap's relfrozenxid to the relfrozenxid of its toast table. I perhaps could create a short DO block that would vacuum freeze just toast tables; it would have to be run in every database. Well, that won't work, because VACUUM can't be executed in a transaction block or function. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On Thu, Apr 07, 2011 at 12:16:55PM -0400, Bruce Momjian wrote: Bruce Momjian wrote: OK, thanks to RhodiumToad on IRC, I was able to determine the cause of the two reported pg_upgrade problems he saw via IRC. It seems toast tables have xids and pg_dump is not preserving the toast relfrozenxids as it should. Heap tables have preserved relfrozenxids, but if you update a heap row but don't change the toast value, and the old heap row is later removed, the toast table can have an older relfrozenxids than the heap table. The fix for this is to have pg_dump preserve toast relfrozenxids, which can be easily added and backpatched. We might want to push a 9.0.4 for this. Second, we need to find a way for people to detect and fix existing systems that have this problem, perhaps looming when the pg_class relfrozenxid passes the toast relfrozenxid, and thirdly, we need to figure out how to get this information to users. Perhaps the communication comes through the 9.0.4 release announcement. I am not sure how to interpret the lack of replies to this email. Either it is confidence, shock, or we told you so. ;-) Your explanation and patch make sense. Seems all too clear in retrospect. Any idea how to correct existing systems? Would VACUUM FREEZE of just the toast tables work? I perhaps could create a short DO block that would vacuum freeze just toast tables; it would have to be run in every database. I see three cases: 1) The pg_class.relfrozenxid that the TOAST table should have received (true relfrozenxid) is still covered by available clog files. Fixable with some combination of pg_class.relfrozenxid twiddling and SET vacuum_freeze_table_age = 0; VACUUM toasttbl. 2) The true relfrozenxid is no longer covered by available clog files. The fix for case 1 will get file foo doesn't exist, reading as zeroes log messages, and we will treat all transactions as uncommitted. Not generally fixable after that has happened. We could probably provide a recipe for checking whether it could have happened given access to a backup from just before the upgrade. 3) Enough transaction xids have elapsed such that the true relfrozenxid is again covered by clog files, but those records are unrelated to the original transactions. Actually, I don't think this can happen, even with the maximum autovacuum_freeze_max_age. I haven't tested those, so I'm sure there's some error in that assessment. nm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Jeff Davis wrote: I have added a personal regression test to show which pg_class.relfrozenxid values are not preserved, and with this patch the only ones not preserved are toast tables used by system tables, which are not copied from the old cluster (FirstNormalObjectId = 16384). I am attaching that old/new pg_class.relfrozenxid diff as well. Any idea how to correct existing systems? Would VACUUM FREEZE of just the toast tables work? VACUUM FREEZE will never set the relfrozenxid backward. If it was never preserved to begin with, I assume that the existing value could be arbitrarily before or after, so it might not be updated. I think that after you VACUUM FREEZE the toast table, then the real oldest frozen xid (as opposed to the bad value in relfrozenxid for the toast table) would have to be the same or newer than that of the heap. Right? That means you could safely copy the heap's relfrozenxid to the relfrozenxid of its toast table. OK, so the only other idea I have is to write some pretty complicated query function that does a sequential scan of each toast table and pulls the earliest xmin/xmax from the tables and use that to set the relfrozenxid (pretty complicated because it has to deal with the freeze horizon and wraparound). I perhaps could create a short DO block that would vacuum freeze just toast tables; it would have to be run in every database. Well, that won't work, because VACUUM can't be executed in a transaction block or function. Good point. The only bright part of this is that missing clog will throw an error so we are not returning incorrect data, and hopefully people will report problems to us when it happens. Ideally I would like to get this patch and correction code out into the field in case more people run into this problem. I know some will, I just don't know how many. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql linestyle unicode and client encoding
Peter Eisentraut pete...@gmx.net wrote: Wouldn't it be better if there were a setting in psql that set the linestyle to unicode only if the client encoding was actually UTF8? Is UTF8 the only client encoding in which we currently support the Unicode character set? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_upgrade automatic testing
Seeing that 9.1-to-9.1 pg_upgrade has apparently been broken for months,
it would probably be good to have some kind of automatic testing for it.
Attached is something I hacked together that at least exposes the
current problems, easily available by typing make check and waiting.
It does not yet fully implement the full testing procedure in the
TESTING file, in particular the diffing of the dumps (well, because you
can't get there yet).
Is that something that people are interested in refining?
(I think it would even be possible under this setup to create special
regression test cases that are only run under the pg_upgrade test run,
to exercise particularly tricky upgrade cases.)
diff --git i/contrib/pg_upgrade/Makefile w/contrib/pg_upgrade/Makefile
index 8f3fd7c..9ec7bc0 100644
--- i/contrib/pg_upgrade/Makefile
+++ w/contrib/pg_upgrade/Makefile
@@ -21,3 +21,6 @@ top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif
+
+check: test.sh
+ MAKE=$(MAKE) bindir=$(bindir) $(SHELL) $
diff --git i/contrib/pg_upgrade/test.sh w/contrib/pg_upgrade/test.sh
index e69de29..a0d459e 100644
--- i/contrib/pg_upgrade/test.sh
+++ w/contrib/pg_upgrade/test.sh
@@ -0,0 +1,36 @@
+set -eux
+
+: ${MAKE=make}
+temp_root=$PWD/tmp_check
+
+temp_install=$temp_root/install
+bindir=$temp_install/$bindir
+PATH=$bindir:$PATH
+export PATH
+
+PGDATA=$temp_root/data
+export PGDATA
+rm -rf $PGDATA $PGDATA.old
+PGPORT=65432
+export PGPORT
+
+logdir=$PWD/log
+rm -r $logdir
+mkdir $logdir
+
+$MAKE -C ../.. install DESTDIR=$temp_install 21 | tee $logdir/install.log
+$MAKE -C ../pg_upgrade_support install DESTDIR=$temp_install 21 | tee -a $logdir/install.log
+$MAKE -C . install DESTDIR=$temp_install 21 | tee -a $logdir/install.log
+
+initdb 21 | tee $logdir/initdb1.log
+pg_ctl start -l $logdir/postmaster.log -w
+$MAKE -C ../.. installcheck 21 | tee $logdir/installcheck.log
+pg_ctl -m fast stop
+
+mv ${PGDATA} ${PGDATA}.old
+
+initdb 21 | tee $logdir/initdb2.log
+
+unset PGPORT
+
+pg_upgrade -d ${PGDATA}.old -D ${PGDATA} -b $bindir -B $bindir
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows build issues
Another issue: ...\src\tools\msvcinstall foo bar bar== was unexpected at this time. This makes it seemingly impossible to install into a standard location such as under C:\Program Files\. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows build issues
On tor, 2011-04-07 at 09:26 +0100, Dave Page wrote: On Wed, Apr 6, 2011 at 6:47 PM, Andrew Dunstan and...@dunslane.net wrote: On 04/06/2011 01:34 PM, Dave Page wrote: On Wed, Apr 6, 2011 at 6:27 PM, Peter Eisentrautpete...@gmx.net wrote: * I have some doubts about whether the SDK is at all needed or whether it would suffice by itself. I went with Visual Studio Express 2008. The SDK is needed with 2008 Express, but not the non-express version. The SDK on it's own should be enough for command line compilation. When you install VC Express 2008 the SDK is installed with it. A separate install is not required, as it was with VCE 2005. Oh nice :-) Well, it's good that you guys are almost as confused as I am. :) Evidently, VC Express 2008 is good by itself, without the separate SDK download. The documentation also appears to imply that the SDK without VC Express would be enough. I can't quite fathom how that would work, because the vcbuild.exe is supplied by VC Express. Then again, the SDK installation offers to install a C++ compiler, but I don't know where it put that. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows build issues
On Thu, Apr 7, 2011 at 22:11, Peter Eisentraut pete...@gmx.net wrote: On tor, 2011-04-07 at 09:26 +0100, Dave Page wrote: On Wed, Apr 6, 2011 at 6:47 PM, Andrew Dunstan and...@dunslane.net wrote: On 04/06/2011 01:34 PM, Dave Page wrote: On Wed, Apr 6, 2011 at 6:27 PM, Peter Eisentrautpete...@gmx.net wrote: * I have some doubts about whether the SDK is at all needed or whether it would suffice by itself. I went with Visual Studio Express 2008. The SDK is needed with 2008 Express, but not the non-express version. The SDK on it's own should be enough for command line compilation. When you install VC Express 2008 the SDK is installed with it. A separate install is not required, as it was with VCE 2005. Oh nice :-) Well, it's good that you guys are almost as confused as I am. :) yeah, it certainly is confusing. Evidently, VC Express 2008 is good by itself, without the separate SDK download. The documentation also appears to imply that the SDK without VC Express would be enough. I can't quite fathom how that would work, because the vcbuild.exe is supplied by VC Express. Then again, the SDK installation offers to install a C++ compiler, but I don't know where it put that. The SDK supplies both the compiler and vcbuild.exe. At least it used to - but since vcbuild isn't in VC 2010, iirc, it is not likely to be in the new version of the SDK either.. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On Thu, Apr 7, 2011 at 3:46 PM, Bruce Momjian br...@momjian.us wrote: Jeff Davis wrote: I have added a personal regression test to show which pg_class.relfrozenxid values are not preserved, and with this patch the only ones not preserved are toast tables used by system tables, which are not copied from the old cluster (FirstNormalObjectId = 16384). I am attaching that old/new pg_class.relfrozenxid diff as well. Any idea how to correct existing systems? Would VACUUM FREEZE of just the toast tables work? VACUUM FREEZE will never set the relfrozenxid backward. If it was never preserved to begin with, I assume that the existing value could be arbitrarily before or after, so it might not be updated. I think that after you VACUUM FREEZE the toast table, then the real oldest frozen xid (as opposed to the bad value in relfrozenxid for the toast table) would have to be the same or newer than that of the heap. Right? That means you could safely copy the heap's relfrozenxid to the relfrozenxid of its toast table. OK, so the only other idea I have is to write some pretty complicated query function that does a sequential scan of each toast table and pulls the earliest xmin/xmax from the tables and use that to set the relfrozenxid (pretty complicated because it has to deal with the freeze horizon and wraparound). I perhaps could create a short DO block that would vacuum freeze just toast tables; it would have to be run in every database. Well, that won't work, because VACUUM can't be executed in a transaction block or function. Good point. The only bright part of this is that missing clog will throw an error so we are not returning incorrect data, and hopefully people will report problems to us when it happens. Ideally I would like to get this patch and correction code out into the field in case more people run into this problem. I know some will, I just don't know how many. ISTM we need to force a minor release once we are sure this has been corrected. We had also probably put out an announcement warning people that have already used pg_upgrade of possible data corruption. I'm not sure exactly what the language around that should be, but this does seem pretty bad. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows build issues
On Thu, Apr 7, 2011 at 4:11 PM, Peter Eisentraut pete...@gmx.net wrote: On tor, 2011-04-07 at 09:26 +0100, Dave Page wrote: On Wed, Apr 6, 2011 at 6:47 PM, Andrew Dunstan and...@dunslane.net wrote: On 04/06/2011 01:34 PM, Dave Page wrote: On Wed, Apr 6, 2011 at 6:27 PM, Peter Eisentrautpete...@gmx.net wrote: * I have some doubts about whether the SDK is at all needed or whether it would suffice by itself. I went with Visual Studio Express 2008. The SDK is needed with 2008 Express, but not the non-express version. The SDK on it's own should be enough for command line compilation. When you install VC Express 2008 the SDK is installed with it. A separate install is not required, as it was with VCE 2005. Oh nice :-) Well, it's good that you guys are almost as confused as I am. :) Evidently, VC Express 2008 is good by itself, without the separate SDK download. The documentation also appears to imply that the SDK without VC Express would be enough. I can't quite fathom how that would work, because the vcbuild.exe is supplied by VC Express. Then again, the SDK installation offers to install a C++ compiler, but I don't know where it put that. It sure would be nice if someone would write a doc patch, or at least a wiki page, explaining all the permutations here... I get the impression it's not that hard to set up if you are reasonable comfortable working in a Windows environment, but it's pretty intimidating if you aren't. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSI bug?
I wrote:
YAMAMOTO Takashi y...@mwd.biglobe.ne.jp wrote:
LOG: could not truncate directory pg_serial: apparent
wraparound
there's some sort of cleanup bug to fix in the predicate
locking's use of SLRU. It may be benign, but we won't really know
until we find it. I'm investigating.
I'm pretty sure I found it. When the number serializable
transactions which need to be tracked gets high enough to push
things to the SLRU summarization, and then drops back down, we
haven't been truncating the head page of the active SLRU region
because if we go back into SLRU summarization that saves us from
zeroing the page again. The problem is that if we don't go back
into SLRU summarization for a long time, we might wrap around to
where SLRU is upset that our head is chasing our tail. This seems
like a bigger problem than we were trying to solve by not truncating
the page.
The issue is complicated a little bit by the fact that the SLRU API
has you specify the *page* for the truncation point, but silently
ignores the request unless the page is in a segment which is past a
segment in use. So adding the number of pages per SLRU segment to
the head page position should do the right thing. But it's all
weird enough that I felt it need a bit of commenting.
While I was there I noticed that we're doing the unnecessary
flushing (so people can glean information about the SLRU activity
from watching the disk files) right before truncating. I switched
the truncation to come before the flushing, since flushing pages to
a file and then deleting that file didn't seem productive.
Attached find a patch which modifies one line of code, switches the
order of two lines of code, and adds comments.
I will add this to the open items for 9.1. Thanks again to YAMAMOTO
Takashi for his rigorous testing.
-Kevin
*** a/src/backend/storage/lmgr/predicate.c
--- b/src/backend/storage/lmgr/predicate.c
***
*** 920,945 CheckPointPredicate(void)
else
{
/*
!* The SLRU is no longer needed. Truncate everything but the
last
!* page. We don't dare to touch the last page in case the SLRU
is
!* taken back to use, and the new tail falls on the same page.
*/
! tailPage = oldSerXidControl-headPage;
oldSerXidControl-headPage = -1;
}
LWLockRelease(OldSerXidLock);
/*
* Flush dirty SLRU pages to disk
*
* This is not actually necessary from a correctness point of view. We
do
* it merely as a debugging aid.
*/
SimpleLruFlush(OldSerXidSlruCtl, true);
-
- /* Truncate away pages that are no longer required */
- SimpleLruTruncate(OldSerXidSlruCtl, tailPage);
}
/**/
--- 920,957
else
{
/*
!* The SLRU is no longer needed. Truncate everything. If we
try to
!* leave the head page around to avoid re-zeroing it, we might
not
!* use the SLRU again until we're past the wrap-around point,
which
!* makes SLRU unhappy.
!*
!* While the API asks you to specify truncation by page, it
silently
!* ignores the request unless the specified page is in a segment
!* past some allocated portion of the SLRU. We don't care which
!* page in a later segment we hit, so just add the number of
pages
!* per segment to the head page to land us *somewhere* in the
next
!* segment.
*/
! tailPage = oldSerXidControl-headPage + SLRU_PAGES_PER_SEGMENT;
oldSerXidControl-headPage = -1;
}
LWLockRelease(OldSerXidLock);
+ /* Truncate away pages that are no longer required */
+ SimpleLruTruncate(OldSerXidSlruCtl, tailPage);
+
/*
* Flush dirty SLRU pages to disk
*
* This is not actually necessary from a correctness point of view. We
do
* it merely as a debugging aid.
+*
+* We're doing this after the truncation to avoid writing pages right
+* before deleting the file in which they sit, which would be completely
+* pointless.
*/
SimpleLruFlush(OldSerXidSlruCtl, true);
}
/**/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Robert Haas robertmh...@gmail.com wrote: ISTM we need to force a minor release once we are sure this has been corrected. We had also probably put out an announcement warning people that have already used pg_upgrade of possible data corruption. I'm not sure exactly what the language around that should be, but this does seem pretty bad. We just used this to upgrade all of our databases to 9.0. Most of those (particularly the databases where data originates) have VACUUM FREEZE ANALYZE run nightly, and we ran this against all databases right after each pg_upgrade. Will that have offered us some protection from this bug? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC 2011 Eager MV implementation proposal
As you people think and may be possible that complete implementation of Eager MVs cannot be completed in summer. So maybe i can pick up the work left to be done in snapshot MVs. I have cloned the repository of pavel baros from https://github.com/pbaros/postgres.git and i will be looking to find whats left out. Could anybody help me in figuring out what is left to be done in snapshot MVs implementation? There are a number of hard problems in getting a working implementation of materialized views that all get ignored by all of the student proposals we get, and what you're talking about doesn't address any of them. As soon as i know the shortcomings of snapshot MVs implementation and once go through the code committed during GSoC, I will revert back with the issues and how would i be tackling them. You really should read all of the messages in the following threads: http://archives.postgresql.org/pgsql-hackers/2010-04/msg00479.php http://archives.postgresql.org/pgsql-hackers/2010-06/msg00743.php http://archives.postgresql.org/pgsql-hackers/2010-07/msg00396.php And the following summaries: http://wiki.postgresql.org/wiki/Materialized_Views_GSoC_2010 http://rhaas.blogspot.com/2010/04/materialized-views-in-postgresql.html And then say how what you're suggesting fits into the issues raised last summer. The theory and way to implement eager MVs are interesting problems. But working on them won't lead toward code that can be committed to PostgreSQL this year.
Re: [HACKERS] pg_upgrade bug found!
On Thu, 2011-04-07 at 15:46 -0400, Bruce Momjian wrote: OK, so the only other idea I have is to write some pretty complicated query function that does a sequential scan of each toast table and pulls the earliest xmin/xmax from the tables and use that to set the relfrozenxid (pretty complicated because it has to deal with the freeze horizon and wraparound). That sounds like the correct way to fix the situation, although it's a little more work to install another function just for this one-time purpose. TransactionIdPrecedes() should already account for wraparound, so I don't think that it will be too complicated (make sure to read every tuple though, not just the ones currently visible). Stepping back a second to make sure I understand the problem: the only problem is that relfrozenxid on the toast table after an upgrade is wrong. Correct? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Add traceback information to PL/Python errors
On Wed, Apr 6, 2011 at 3:37 PM, Peter Eisentraut pete...@gmx.net wrote: Add traceback information to PL/Python errors This mimics the traceback information the Python interpreter prints with exceptions. Jan Urbański On my system this spits out a warning: plpython.c: In function ‘PLy_traceback’: plpython.c:4487: warning: ‘s’ may be used uninitialized in this function plpython.c:4487: note: ‘s’ was declared here That appears to be a live bug, unless it's guaranteed that lineno will always be 0. Also, the loop test should really be written as current lineno, rather than current != lineno, just in case we should manage to pass a lineno 0, which with the current code would go into the tank and spin. This part looks pretty sketchy, too: while (s isspace((unsigned char) *s)) s++; Perhaps we meant to test *s here. It's hard to believe that we're really intending to test whether the pointer has fallen off the end of the address space and wrapped around to NULL. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade automatic testing
On Thu, Apr 7, 2011 at 4:02 PM, Peter Eisentraut pete...@gmx.net wrote: Seeing that 9.1-to-9.1 pg_upgrade has apparently been broken for months, it would probably be good to have some kind of automatic testing for it. Attached is something I hacked together that at least exposes the current problems, easily available by typing make check and waiting. It does not yet fully implement the full testing procedure in the TESTING file, in particular the diffing of the dumps (well, because you can't get there yet). Is that something that people are interested in refining? (I think it would even be possible under this setup to create special regression test cases that are only run under the pg_upgrade test run, to exercise particularly tricky upgrade cases.) I think it's a worthwhile thing to do, but right now I think it would be more helpful if you could help fix the breakage your patch created, rather than working on new stuff. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Add traceback information to PL/Python errors
On 07/04/11 23:01, Robert Haas wrote: On Wed, Apr 6, 2011 at 3:37 PM, Peter Eisentraut pete...@gmx.net wrote: Add traceback information to PL/Python errors This mimics the traceback information the Python interpreter prints with exceptions. Jan Urbański On my system this spits out a warning: plpython.c: In function ‘PLy_traceback’: plpython.c:4487: warning: ‘s’ may be used uninitialized in this function plpython.c:4487: note: ‘s’ was declared here That appears to be a live bug, unless it's guaranteed that lineno will always be 0. lineno should be 0, unless Python is trying to tell us that the code frame originates from before the function. Also, the loop test should really be written as current lineno, rather than current != lineno, just in case we should manage to pass a lineno 0, which with the current code would go into the tank and spin. Yeah, good point. This part looks pretty sketchy, too: while (s isspace((unsigned char) *s)) s++; Perhaps we meant to test *s here. It's hard to believe that we're really intending to test whether the pointer has fallen off the end of the address space and wrapped around to NULL. Gah, so short a function and so many things that I managed to get wrong. There's also this: http://archives.postgresql.org/pgsql-hackers/2011-04/msg00334.php Jan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Jeff Davis wrote: On Thu, 2011-04-07 at 15:46 -0400, Bruce Momjian wrote: OK, so the only other idea I have is to write some pretty complicated query function that does a sequential scan of each toast table and pulls the earliest xmin/xmax from the tables and use that to set the relfrozenxid (pretty complicated because it has to deal with the freeze horizon and wraparound). That sounds like the correct way to fix the situation, although it's a little more work to install another function just for this one-time purpose. TransactionIdPrecedes() should already account for wraparound, so I don't think that it will be too complicated (make sure to read every tuple though, not just the ones currently visible). I want to avoid anything that requires a compile because they are hard for many sites to install so TransactionIdPrecedes() is out. We will need to do this in PL/pgSQL probably. Stepping back a second to make sure I understand the problem: the only problem is that relfrozenxid on the toast table after an upgrade is wrong. Correct? Yes, it was not restored from the old cluster. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Jeff Davis wrote: On Thu, 2011-04-07 at 15:46 -0400, Bruce Momjian wrote: OK, so the only other idea I have is to write some pretty complicated query function that does a sequential scan of each toast table and pulls the earliest xmin/xmax from the tables and use that to set the relfrozenxid (pretty complicated because it has to deal with the freeze horizon and wraparound). That sounds like the correct way to fix the situation, although it's a little more work to install another function just for this one-time purpose. TransactionIdPrecedes() should already account for wraparound, so I don't think that it will be too complicated (make sure to read every tuple though, not just the ones currently visible). Stepping back a second to make sure I understand the problem: the only problem is that relfrozenxid on the toast table after an upgrade is wrong. Correct? One minimal solution might be to set the toast relfozenxid to match the heap frozenxid? Ideas? It is not 100% accurate but it might help. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Robert Haas wrote: Well, that won't work, because VACUUM can't be executed in a transaction block or function. Good point. The only bright part of this is that missing clog will throw an error so we are not returning incorrect data, and hopefully people will report problems to us when it happens. Ideally I would like to get this patch and correction code out into the field in case more people run into this problem. ?I know some will, I just don't know how many. ISTM we need to force a minor release once we are sure this has been corrected. We had also probably put out an announcement warning people that have already used pg_upgrade of possible data corruption. I'm not sure exactly what the language around that should be, but this does seem pretty bad. Yep, pretty bad it is. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] help: collation support on Windows
On fre, 2011-02-25 at 21:32 +0200, Peter Eisentraut wrote:
According to the online documentation, the APIs are there:
http://msdn.microsoft.com/en-ca/library/a7cwbx4t.aspx
Now we'd need someone brave try to make it work. The starting point
would be to define HAVE_LOCALE_T and then make it build. Microsoft has
all the relevant functions and types with an underscore in front
(_strcoll_l, etc.), so some extra #defining will probably be necessary.
OK, I got that working now. Patch attached.
Also, initdb will need to be patched to get a list of OS locales to
populate the pg_collation catalog with.
That still needs work, but you can run CREATE COLLATION manually.
Finally, a regression test customized for Windows, but I can help with
that later.
If you doctor the existing linux test to create appropriately named
collations before running the actual tests, and you hack the
vcregress.pl driver script to run the tests in UTF8 instead of
SQL_ASCII, then all the tests except the Turkish case conversion tests
pass. So looks pretty good so far.
diff --git a/src/backend/utils/adt/pg_locale.c b/src/backend/utils/adt/pg_locale.c
index 163856d..ff7de38 100644
--- a/src/backend/utils/adt/pg_locale.c
+++ b/src/backend/utils/adt/pg_locale.c
@@ -962,8 +962,12 @@ pg_newlocale_from_collation(Oid collid)
if (strcmp(collcollate, collctype) == 0)
{
/* Normal case where they're the same */
+#ifndef WIN32
result = newlocale(LC_COLLATE_MASK | LC_CTYPE_MASK, collcollate,
NULL);
+#else
+ result = _create_locale(LC_ALL, collcollate);
+#endif
if (!result)
ereport(ERROR,
(errcode_for_file_access(),
@@ -972,6 +976,7 @@ pg_newlocale_from_collation(Oid collid)
}
else
{
+#ifndef WIN32
/* We need two newlocale() steps */
locale_t loc1;
@@ -987,6 +992,9 @@ pg_newlocale_from_collation(Oid collid)
(errcode_for_file_access(),
errmsg(could not create locale \%s\: %m,
collctype)));
+#else
+ elog(ERROR, not supported);
+#endif
}
cache_entry-locale = result;
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 3587fe4..04b0326 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -1374,7 +1374,10 @@ varstr_cmp(char *arg1, int len1, char *arg2, int len2, Oid collid)
((LPWSTR) a2p)[r] = 0;
errno = 0;
- result = wcscoll((LPWSTR) a1p, (LPWSTR) a2p);
+ if (mylocale)
+result = _wcscoll_l((LPWSTR) a1p, (LPWSTR) a2p, mylocale);
+ else
+result = wcscoll((LPWSTR) a1p, (LPWSTR) a2p);
if (result == 2147483647) /* _NLSCMPERROR; missing from mingw
* headers */
ereport(ERROR,
diff --git a/src/include/pg_config.h.win32 b/src/include/pg_config.h.win32
index 79b8036..8eae0b4 100644
--- a/src/include/pg_config.h.win32
+++ b/src/include/pg_config.h.win32
@@ -683,3 +683,5 @@
/* Define to empty if the keyword `volatile' does not work. Warning: valid
code using `volatile' can become incorrect without. Disable with care. */
/* #undef volatile */
+
+#define HAVE_LOCALE_T 1
diff --git a/src/include/utils/pg_locale.h b/src/include/utils/pg_locale.h
index 4c72fd0..370d691 100644
--- a/src/include/utils/pg_locale.h
+++ b/src/include/utils/pg_locale.h
@@ -17,6 +17,17 @@
#include xlocale.h
#endif
+#ifdef WIN32
+#define locale_t _locale_t
+#define towlower_l _towlower_l
+#define towupper_l _towupper_l
+#define toupper_l _toupper_l
+#define tolower_l _tolower_l
+#define iswalnum_l _iswalnum_l
+#define isalnum_l _isalnum_l
+#define strcoll_l _strcoll_l
+#endif
+
#include utils/guc.h
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Bruce Momjian wrote: Robert Haas wrote: Well, that won't work, because VACUUM can't be executed in a transaction block or function. Good point. The only bright part of this is that missing clog will throw an error so we are not returning incorrect data, and hopefully people will report problems to us when it happens. Ideally I would like to get this patch and correction code out into the field in case more people run into this problem. ?I know some will, I just don't know how many. ISTM we need to force a minor release once we are sure this has been corrected. We had also probably put out an announcement warning people that have already used pg_upgrade of possible data corruption. I'm not sure exactly what the language around that should be, but this does seem pretty bad. Yep, pretty bad it is. The bug exists because I did not realize that the toast relfrozenxid is tracked independently of the heap, until the IRC report diagnosis. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade automatic testing
On tor, 2011-04-07 at 17:03 -0400, Robert Haas wrote: I think it's a worthwhile thing to do, but right now I think it would be more helpful if you could help fix the breakage your patch created, rather than working on new stuff. This is part of that. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
On ons, 2011-04-06 at 11:49 -0400, Noah Misch wrote: Peter, were you planning to complete this? I can take a swing at it, if it would be helpful. Help is always welcome. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On Thu, 2011-04-07 at 17:06 -0400, Bruce Momjian wrote: I want to avoid anything that requires a compile because they are hard for many sites to install so TransactionIdPrecedes() is out. We will need to do this in PL/pgSQL probably. PL/pgSQL can't see dead rows, so that would not be correct. It's guaranteed to be the same value you see from the heap or newer; because if it's not visible in the heap, it's not going to be visible in the toast table. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Jeff Davis wrote: On Thu, 2011-04-07 at 17:06 -0400, Bruce Momjian wrote: I want to avoid anything that requires a compile because they are hard for many sites to install so TransactionIdPrecedes() is out. We will need to do this in PL/pgSQL probably. PL/pgSQL can't see dead rows, so that would not be correct. It's guaranteed to be the same value you see from the heap or newer; because if it's not visible in the heap, it's not going to be visible in the toast table. Well, frankly all we need to do is set those hint bits before the clog gets remove, so maybe just a SELECT * would do the trick! That and maybe set the relfrozenxid to match the heap. It is there now or more people would be reporting problems. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Bruce Momjian wrote: Jeff Davis wrote: On Thu, 2011-04-07 at 17:06 -0400, Bruce Momjian wrote: I want to avoid anything that requires a compile because they are hard for many sites to install so TransactionIdPrecedes() is out. We will need to do this in PL/pgSQL probably. PL/pgSQL can't see dead rows, so that would not be correct. It's guaranteed to be the same value you see from the heap or newer; because if it's not visible in the heap, it's not going to be visible in the toast table. Well, frankly all we need to do is set those hint bits before the clog gets remove, so maybe just a SELECT * would do the trick! That and maybe set the relfrozenxid to match the heap. It is there now or more people would be reporting problems. Clarification, the clog is there now or more people would be reporting problems. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Bruce Momjian br...@momjian.us wrote: all we need to do is set those hint bits before the clog gets remove, so maybe just a SELECT * would do the trick! Does that mean that those experiencing the problem are failing to do the vacuumdb run which is recommended in the pg_upgrade instructions? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: all we need to do is set those hint bits before the clog gets remove, so maybe just a SELECT * would do the trick! Does that mean that those experiencing the problem are failing to do the vacuumdb run which is recommended in the pg_upgrade instructions? You know, I looked at that, but I don't think that is going to save me. :-( It says: Upgrade complete | Optimizer statistics are not transferred by pg_upgrade | so consider running: | vacuumdb --all --analyze-only | on the newly-upgraded cluster. | Running this script will delete the old cluster's data files: | /usr/var/local/pgdev/pgfoundry/pg_migrator/pg_migrator/delete_old_cluster.sh We recommend 'vacuumdb --all --analyze-only' which I assume only samples random pages and does not set all the hint bits. In fact, you can't even analyze TOAST tables: test= ANALYZE pg_toast.pg_toast_3596; WARNING: skipping pg_toast_3596 --- cannot analyze non-tables or special system tables ANALYZE but you can SELECT from them: chunk_id | chunk_seq | chunk_data --+---+ (0 rows) Also, if we force VACUUM FREEZE on the toast tables we would have no need to advance their relfrozenxids because all the xids would be fixed. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On Thu, 2011-04-07 at 12:38 -0700, Jeff Davis wrote: Any idea how to correct existing systems? Would VACUUM FREEZE of just the toast tables work? VACUUM FREEZE will never set the relfrozenxid backward. If it was never preserved to begin with, I assume that the existing value could be arbitrarily before or after, so it might not be updated. Now that I understand the problem a little better, I think VACUUM FREEZE might work, after all. Originally, I thought that the toast table's relfrozenxid could be some arbitrarily wrong value. But actually, the CREATE TABLE is issued after the xid of the new cluster has already been advanced to the xid of the old cluster, so it should be a somewhat reasonable value. That means that VACUUM FREEZE of the toast table, if there are no concurrent transactions, will freeze all of the tuples; and the newFrozenXid should always be seen as newer than the existing (and wrong) relfrozenxid. Then, it will set relfrozenxid to newFrozenXid and everything should be fine. Right? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Jeff Davis wrote: On Thu, 2011-04-07 at 12:38 -0700, Jeff Davis wrote: Any idea how to correct existing systems? Would VACUUM FREEZE of just the toast tables work? VACUUM FREEZE will never set the relfrozenxid backward. If it was never preserved to begin with, I assume that the existing value could be arbitrarily before or after, so it might not be updated. Now that I understand the problem a little better, I think VACUUM FREEZE might work, after all. Good. I don't want to be inventing something complex if I can avoid it. Simple is good, espeically if admins panic. I would rather simple and longer than short but complex :-) Originally, I thought that the toast table's relfrozenxid could be some arbitrarily wrong value. But actually, the CREATE TABLE is issued after the xid of the new cluster has already been advanced to the xid of the old cluster, so it should be a somewhat reasonable value. Yes, it will be reasonable. That means that VACUUM FREEZE of the toast table, if there are no concurrent transactions, will freeze all of the tuples; and the newFrozenXid should always be seen as newer than the existing (and wrong) relfrozenxid. Then, it will set relfrozenxid to newFrozenXid and everything should be fine. Right? Right. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade automatic testing
On Thu, Apr 7, 2011 at 5:20 PM, Peter Eisentraut pete...@gmx.net wrote: On tor, 2011-04-07 at 17:03 -0400, Robert Haas wrote: I think it's a worthwhile thing to do, but right now I think it would be more helpful if you could help fix the breakage your patch created, rather than working on new stuff. This is part of that. It's related. But we can release beta1 without improving the regression testing framework for pg_upgrade. We cannot release beta1 with pg_upgrade broken. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC 2011 Eager MV implementation proposal
On Thu, Apr 7, 2011 at 4:49 PM, AAMIR KHAN ak4u2...@gmail.com wrote: As you people think and may be possible that complete implementation of Eager MVs cannot be completed in summer. So maybe i can pick up the work left to be done in snapshot MVs. I have cloned the repository of pavel baros from https://github.com/pbaros/postgres.git and i will be looking to find whats left out. You had better start by getting a clear statement from Pavel as to whether he wishes to release the code in that repository under the PostgreSQL license. I am not sure that he ever formally submitted it. I still think you should start with an easier project. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On Thu, Apr 7, 2011 at 5:52 PM, Bruce Momjian br...@momjian.us wrote: Jeff Davis wrote: On Thu, 2011-04-07 at 12:38 -0700, Jeff Davis wrote: Any idea how to correct existing systems? Would VACUUM FREEZE of just the toast tables work? VACUUM FREEZE will never set the relfrozenxid backward. If it was never preserved to begin with, I assume that the existing value could be arbitrarily before or after, so it might not be updated. Now that I understand the problem a little better, I think VACUUM FREEZE might work, after all. Good. I don't want to be inventing something complex if I can avoid it. Simple is good, espeically if admins panic. I would rather simple and longer than short but complex :-) Originally, I thought that the toast table's relfrozenxid could be some arbitrarily wrong value. But actually, the CREATE TABLE is issued after the xid of the new cluster has already been advanced to the xid of the old cluster, so it should be a somewhat reasonable value. Yes, it will be reasonable. That means that VACUUM FREEZE of the toast table, if there are no concurrent transactions, will freeze all of the tuples; and the newFrozenXid should always be seen as newer than the existing (and wrong) relfrozenxid. Then, it will set relfrozenxid to newFrozenXid and everything should be fine. Right? Right. This depends on how soon after the upgrade VACUUM FREEZE is run, doesn't it? If the XID counter has advanced too far... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Add traceback information to PL/Python errors
On Thu, Apr 7, 2011 at 5:06 PM, Jan Urbański wulc...@wulczer.org wrote: On 07/04/11 23:01, Robert Haas wrote: On Wed, Apr 6, 2011 at 3:37 PM, Peter Eisentraut pete...@gmx.net wrote: Add traceback information to PL/Python errors This mimics the traceback information the Python interpreter prints with exceptions. Jan Urbański On my system this spits out a warning: plpython.c: In function ‘PLy_traceback’: plpython.c:4487: warning: ‘s’ may be used uninitialized in this function plpython.c:4487: note: ‘s’ was declared here That appears to be a live bug, unless it's guaranteed that lineno will always be 0. lineno should be 0, unless Python is trying to tell us that the code frame originates from before the function. Also, the loop test should really be written as current lineno, rather than current != lineno, just in case we should manage to pass a lineno 0, which with the current code would go into the tank and spin. Yeah, good point. This part looks pretty sketchy, too: while (s isspace((unsigned char) *s)) s++; Perhaps we meant to test *s here. It's hard to believe that we're really intending to test whether the pointer has fallen off the end of the address space and wrapped around to NULL. Gah, so short a function and so many things that I managed to get wrong. Patch? There's also this: http://archives.postgresql.org/pgsql-hackers/2011-04/msg00334.php Yep. I am assuming Peter will look at that one. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Robert Haas wrote: On Thu, Apr 7, 2011 at 5:52 PM, Bruce Momjian br...@momjian.us wrote: Jeff Davis wrote: On Thu, 2011-04-07 at 12:38 -0700, Jeff Davis wrote: Any idea how to correct existing systems? ?Would VACUUM FREEZE of just the toast tables work? VACUUM FREEZE will never set the relfrozenxid backward. If it was never preserved to begin with, I assume that the existing value could be arbitrarily before or after, so it might not be updated. Now that I understand the problem a little better, I think VACUUM FREEZE might work, after all. Good. ?I don't want to be inventing something complex if I can avoid it. Simple is good, espeically if admins panic. ?I would rather simple and longer than short but complex ?:-) Originally, I thought that the toast table's relfrozenxid could be some arbitrarily wrong value. But actually, the CREATE TABLE is issued after the xid of the new cluster has already been advanced to the xid of the old cluster, so it should be a somewhat reasonable value. Yes, it will be reasonable. That means that VACUUM FREEZE of the toast table, if there are no concurrent transactions, will freeze all of the tuples; and the newFrozenXid should always be seen as newer than the existing (and wrong) relfrozenxid. Then, it will set relfrozenxid to newFrozenXid and everything should be fine. Right? Right. This depends on how soon after the upgrade VACUUM FREEZE is run, doesn't it? If the XID counter has advanced too far... Well, I assume VACUUM FREEZE is going to sequential scan the table and replace every xid. If the clog is gone, well, we have problems. I think the IRC reporter pulled the clog files from a backup. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC 2011 Eager MV implementation proposal
You had better start by getting a clear statement from Pavel as to whether he wishes to release the code in that repository under the PostgreSQL license. I am not sure that he ever formally submitted it. I don't think it's reasonable for a student to do that. That really needs to be up to us in the project. I still think you should start with an easier project. Can you give him some suggestions? He's interested in materialized views; what chunk is biteable-off? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Add traceback information to PL/Python errors
On 08/04/11 00:25, Robert Haas wrote:
On Thu, Apr 7, 2011 at 5:06 PM, Jan Urbański wulc...@wulczer.org wrote:
On 07/04/11 23:01, Robert Haas wrote:
On Wed, Apr 6, 2011 at 3:37 PM, Peter Eisentraut pete...@gmx.net wrote:
Add traceback information to PL/Python errors
This mimics the traceback information the Python interpreter prints
with exceptions.
Jan Urbański
On my system this spits out a warning:
plpython.c: In function ‘PLy_traceback’:
plpython.c:4487: warning: ‘s’ may be used uninitialized in this function
plpython.c:4487: note: ‘s’ was declared here
That appears to be a live bug, unless it's guaranteed that lineno will
always be 0.
lineno should be 0, unless Python is trying to tell us that the code
frame originates from before the function.
Also, the loop test should really be written as current lineno,
rather than current != lineno, just in case we should manage to pass a
lineno 0, which with the current code would go into the tank and
spin.
Yeah, good point.
This part looks pretty sketchy, too:
while (s isspace((unsigned char) *s))
s++;
Perhaps we meant to test *s here. It's hard to believe that we're
really intending to test whether the pointer has fallen off the end of
the address space and wrapped around to NULL.
Gah, so short a function and so many things that I managed to get wrong.
Patch?
Attached.
There's also this:
http://archives.postgresql.org/pgsql-hackers/2011-04/msg00334.php
Yep. I am assuming Peter will look at that one.
I guess so. This only fixes the things you noticed.
Jan
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index 9352580..47d898a 100644
*** a/src/pl/plpython/plpython.c
--- b/src/pl/plpython/plpython.c
*** cleanup:
*** 4484,4495
static char *
get_source_line(const char *src, int lineno)
{
! const char *s;
! const char *next;
! int current = 0;
! next = src;
! while (current != lineno)
{
s = next;
next = strchr(s + 1, '\n');
--- 4484,4494
static char *
get_source_line(const char *src, int lineno)
{
! const char *s = NULL;
! const char *next = src;
! int current = 0;
! while (current lineno)
{
s = next;
next = strchr(s + 1, '\n');
*** get_source_line(const char *src, int lin
*** 4501,4507
if (current != lineno)
return NULL;
! while (s isspace((unsigned char) *s))
s++;
if (next == NULL)
--- 4500,4506
if (current != lineno)
return NULL;
! while (*s isspace((unsigned char) *s))
s++;
if (next == NULL)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] building on WIndows 7
It appears that the command processor on Windows 7 has a few quirks that we need to deal with. If you see odd buildfarm or MSVC build script failures on W7 that's a likely cause. I'm digging further and should have some fixes before long. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql linestyle unicode and client encoding
On Thu, Apr 07, 2011 at 02:49:29PM -0500, Kevin Grittner wrote: Peter Eisentraut pete...@gmx.net wrote: Wouldn't it be better if there were a setting in psql that set the linestyle to unicode only if the client encoding was actually UTF8? Is UTF8 the only client encoding in which we currently support the Unicode character set? Yes. When I wrote the patch, I did the linestyle abstraction as a simple set of strings; we therefore store the line drawing characters as raw UTF-8 octets rather than UCS codepoints, stored as regular char* strings. UCS codepoints would be simpler, and would allow transparent recoding to the user's locale codeset when doing e.g. wprintf / fputwc, which might make it simpler to support UTF-16 and other non-UTF-8 UCS encodings. For systems with libcs like GNU libc, where all locales are implemented in terms of USC, it will also allow transparent recoding and/ or transliteration in non-Unicode locales as well. The downside is that it assumes that the system has functional wide character support, while the current implementation requires no special support at all. I don't know if this would be a big issue or not. It should also be possible to use VT100 line drawing characters for most common terminal emulators (xterm and compatible), which would give line drawing characters independent of locale. But this is terminal- dependent. Regards, Roger -- .''`. Roger Leigh : :' : Debian GNU/Linux http://people.debian.org/~rleigh/ `. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/ `-GPG Public Key: 0x25BFB848 Please GPG sign your mail. signature.asc Description: Digital signature
Re: [HACKERS] pg_upgrade bug found!
Bruce Momjian wrote: Yes, it will be reasonable. That means that VACUUM FREEZE of the toast table, if there are no concurrent transactions, will freeze all of the tuples; and the newFrozenXid should always be seen as newer than the existing (and wrong) relfrozenxid. Then, it will set relfrozenxid to newFrozenXid and everything should be fine. Right? Right. This depends on how soon after the upgrade VACUUM FREEZE is run, doesn't it? If the XID counter has advanced too far... Well, I assume VACUUM FREEZE is going to sequential scan the table and replace every xid. If the clog is gone, well, we have problems. I think the IRC reporter pulled the clog files from a backup. So I think we have four possible approaches to correct databases: 1) SELECT * to set the hint bits 2) VACUUM to set the hint bits 3) VACUUM FREEZE to remove the old xids 4) some complicated function I don't like #4, and I think I can script #2 and #3 in psql by using COPY to create a VACUUM script and then run it with \i. #1 is easy in a DO block with PL/pgSQL. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
On Thu, 2011-04-07 at 20:14 -0400, Bruce Momjian wrote: So I think we have four possible approaches to correct databases: 1) SELECT * to set the hint bits 2) VACUUM to set the hint bits 3) VACUUM FREEZE to remove the old xids 4) some complicated function I don't like #4, and I think I can script #2 and #3 in psql by using COPY to create a VACUUM script and then run it with \i. #1 is easy in a DO block with PL/pgSQL. The only one that sounds very reasonable to me is #3. If there are any xids older than the relfrozenxid, we need to get rid of them. If there is some reason that doesn't work, I suppose we can consider the alternatives. But I don't like the hint-bit-setting approach much. What if the xmax is really a transaction that got an exclusive lock on the tuple, rather than actually deleting it? Are you sure that a SELECT (or even a normal VACUUM) would get rid of that xid, or might something still try to look it up in the clog later? Not only that, but hint-bit-setting is not WAL-logged, so you'd really have to do a checkpoint afterward. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Jeff Davis wrote: On Thu, 2011-04-07 at 20:14 -0400, Bruce Momjian wrote: So I think we have four possible approaches to correct databases: 1) SELECT * to set the hint bits 2) VACUUM to set the hint bits 3) VACUUM FREEZE to remove the old xids 4) some complicated function I don't like #4, and I think I can script #2 and #3 in psql by using COPY to create a VACUUM script and then run it with \i. #1 is easy in a DO block with PL/pgSQL. The only one that sounds very reasonable to me is #3. If there are any xids older than the relfrozenxid, we need to get rid of them. If there is some reason that doesn't work, I suppose we can consider the alternatives. But I don't like the hint-bit-setting approach much. What if the xmax is really a transaction that got an exclusive lock on the tuple, rather than actually deleting it? Are you sure that a SELECT (or even a normal VACUUM) would get rid of that xid, or might something still try to look it up in the clog later? Not only that, but hint-bit-setting is not WAL-logged, so you'd really have to do a checkpoint afterward. Glad you said that! Here is a script which does what we want: -- This script fixes data in pre-PG 9.0.4 and pre-8.4.8 -- servers that were upgraded by pg_upgrade and pg_migrator. -- Run the script using psql for every database in the cluster, -- except 'template0', e.g. -- psql -f pg_upgrade_fix dbname -- It will not lock any tables but will generate I/O. -- SET vacuum_freeze_min_age = 0; SET vacuum_freeze_table_age = 0; CREATE TEMPORARY TABLE pg_upgrade_fix AS SELECT 'VACUUM FREEZE pg_toast.' || quote_ident(relname) || ';' FROMpg_class c, pg_namespace n WHERE c.relnamespace = n.oid AND n.nspname = 'pg_toast' AND c.relkind = 't'; \copy pg_upgrade_fix TO 'pg_upgrade_fix.sql'; \i pg_upgrade_fix.sql DROP TABLE pg_upgrade_fix; Looks pretty simple to copy/paste and use. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Robert Haas robertmh...@gmail.com writes: On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan and...@dunslane.net wrote: That doesn't mean we should arbitrarily break compatibility with pl/sql, nor that we should feel free to add on warts such as $varname that are completely at odds with the style of the rest of the language. That doesn't do anything except produce a mess. Well, what it does is avoid breaking compatibility with previous versions of PostgreSQL. I think that actually does have some value. Otherwise, we'd be folding to upper-case by default. Well, if we're going to consider 100% backwards compatibility a must, then we should just stick with what the submitted patch does, ie, unqualified names are matched first to query columns, and to parameters only if there's no column match. This is also per spec if I interpreted Peter's comments correctly. The whole thread started because I suggested that throwing an error for ambiguous cases might be a better design in the long run, but apparently long term ease of code maintenance is far down our list of priorities ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade bug found!
Noah Misch wrote: On Thu, Apr 07, 2011 at 12:16:55PM -0400, Bruce Momjian wrote: Bruce Momjian wrote: OK, thanks to RhodiumToad on IRC, I was able to determine the cause of the two reported pg_upgrade problems he saw via IRC. It seems toast tables have xids and pg_dump is not preserving the toast relfrozenxids as it should. Heap tables have preserved relfrozenxids, but if you update a heap row but don't change the toast value, and the old heap row is later removed, the toast table can have an older relfrozenxids than the heap table. The fix for this is to have pg_dump preserve toast relfrozenxids, which can be easily added and backpatched. We might want to push a 9.0.4 for this. Second, we need to find a way for people to detect and fix existing systems that have this problem, perhaps looming when the pg_class relfrozenxid passes the toast relfrozenxid, and thirdly, we need to figure out how to get this information to users. Perhaps the communication comes through the 9.0.4 release announcement. I am not sure how to interpret the lack of replies to this email. Either it is confidence, shock, or we told you so. ;-) Your explanation and patch make sense. Seems all too clear in retrospect. Yeah, like duh for me. Any idea how to correct existing systems? Would VACUUM FREEZE of just the toast tables work? I perhaps could create a short DO block that would vacuum freeze just toast tables; it would have to be run in every database. I see three cases: 1) The pg_class.relfrozenxid that the TOAST table should have received (true relfrozenxid) is still covered by available clog files. Fixable with some combination of pg_class.relfrozenxid twiddling and SET vacuum_freeze_table_age = 0; VACUUM toasttbl. Right, VACUUM FREEZE. I now see I don't need to set vacuum_freeze_table_age if I use the FREEZE keyword, e.g. gram.y has: if (n-options VACOPT_FREEZE) n-freeze_min_age = n-freeze_table_age = 0; 2) The true relfrozenxid is no longer covered by available clog files. The fix for case 1 will get file foo doesn't exist, reading as zeroes log messages, and we will treat all transactions as uncommitted. Uh, are you sure? I think it would return an error message about a missing clog file for the query; here is a report of a case not related to pg_upgrade: http://archives.postgresql.org/pgsql-admin/2010-09/msg00109.php Not generally fixable after that has happened. We could probably provide a recipe for checking whether it could have happened given access to a backup from just before the upgrade. The IRC folks pulled the clog files off of backups. 3) Enough transaction xids have elapsed such that the true relfrozenxid is again covered by clog files, but those records are unrelated to the original transactions. Actually, I don't think this can happen, even with the maximum autovacuum_freeze_max_age. Yes, I don't think that can happen either. One concern I have is that existing heap tables are protecting clog files, but once those are frozen, the system might remove clog files not realizing it has to freeze the heap tables too. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Apr 7, 2011, at 6:58 PM, Tom Lane wrote: Well, if we're going to consider 100% backwards compatibility a must, then we should just stick with what the submitted patch does, ie, unqualified names are matched first to query columns, and to parameters only if there's no column match. This is also per spec if I interpreted Peter's comments correctly. The whole thread started because I suggested that throwing an error for ambiguous cases might be a better design in the long run, but apparently long term ease of code maintenance is far down our list of priorities ... I agree with you that it should throw an error, at least optionally. Could we not recycle the settings that control this for plpgsql functions? Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Thu, Apr 7, 2011 at 9:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan and...@dunslane.net wrote: That doesn't mean we should arbitrarily break compatibility with pl/sql, nor that we should feel free to add on warts such as $varname that are completely at odds with the style of the rest of the language. That doesn't do anything except produce a mess. Well, what it does is avoid breaking compatibility with previous versions of PostgreSQL. I think that actually does have some value. Otherwise, we'd be folding to upper-case by default. Well, if we're going to consider 100% backwards compatibility a must, then we should just stick with what the submitted patch does, ie, unqualified names are matched first to query columns, and to parameters only if there's no column match. This is also per spec if I interpreted Peter's comments correctly. The whole thread started because I suggested that throwing an error for ambiguous cases might be a better design in the long run, but apparently long term ease of code maintenance is far down our list of priorities ... Not really. But if you're going to shout down my proposal because it's not in the spec, then it's a bit hard to see how you can argue that we should implement your non-spec-compliant behavior instead, especially at the cost of a painful backward compatibility break. I actually am 100% in agreement with you that allowing ambiguous references to resolve either way is a recipe for bugs, bugs, and more bugs. But breaking people's code is not a better answer. We still have people on 8.2 because the pain of upgrading to 8.3 is more than they can bear, and how many releases have we spent trying to get standard_conforming_strings worked out? I admit this probably wouldn't be as bad, but we've managed to put out several releases in a row now that are relatively painless to upgrade between, and I think that's a trend we should try to keep going. I am halfway tempted to say that we need to invent our own procedural language that is designed not for compatibility with the SQL standard or Oracle, but for non-crappiness. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC 2011 Eager MV implementation proposal
On Thu, Apr 7, 2011 at 6:31 PM, Josh Berkus j...@agliodbs.com wrote: You had better start by getting a clear statement from Pavel as to whether he wishes to release the code in that repository under the PostgreSQL license. I am not sure that he ever formally submitted it. I don't think it's reasonable for a student to do that. That really needs to be up to us in the project. My point is that we cannot accept code unless it's clear that the author wishes to contribute it under our license. We cannot assume that code from any random source on the Internet falls into that category. I still think you should start with an easier project. Can you give him some suggestions? He's interested in materialized views; what chunk is biteable-off? Materialized views is a big project that will be quite invasive and touch many areas of the system; it will be comparable to SQL/MED, in that both add a new relkind. I don't think there's any chunk that can be broken off there that will be reasonable for a first patch. I made some other suggestions via IM last night, hence my use of the word still. If anyone has ideas, toss 'em out... one idea I had for a project, if it's not stepping on Greg Stark's toes, is to pick up the EXPLAIN (RESOURCE) patch that he was working on a while back and finish it off. Mind you, if he wants to work on materialized views, he's more than welcome to do so, and if the patch is awesome, I will be as happy as anyone. But it took me two years to work up to handling patches of that complexity, so I think it's reasonable to suggest that he might also need more than zero ramp-up. It's to no one's benefit if new contributors try a project that is too hard, get discouraged, and give up. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Robert Haas wrote: I am halfway tempted to say that we need to invent our own procedural language that is designed not for compatibility with the SQL standard or Oracle, but for non-crappiness. I'm way ahead of you on that one. -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
