Fwd: Re: [HACKERS] make check-world regress failed
Hi, Thanks Andres, i686 check-world passed with your atomic-uint64-alignment.patch. Vladimir Kokovic Belgrade Serbia, 9.Jan 2015 --- Forwarded message --- From: Andres Freund and...@2ndquadrant.com To: Heikki Linnakangas hlinnakan...@vmware.com Cc: Vladimir Koković vladimir.koko...@gmail.com, pgsql-hackers@postgresql.org pgsql-hackers@postgresql.org Subject: Re: [HACKERS] make check-world regress failed Date: Thu, 08 Jan 2015 21:46:35 +0100 On 2014-12-04 16:38:45 +0200, Heikki Linnakangas wrote: On 11/23/2014 08:37 PM, Vladimir Koković wrote: PostgreSQL check-world regress failed with current GIT HEAD on my Kubuntu 14.10. uname -a Linux vlD-kuci 3.16.0-24-generic #32-Ubuntu SMP Tue Oct 28 13:13:18 UTC 2014 i686 athlon i686 GNU/Linux gdb -d /home/src/postgresql-devel/postgresql-git/postgresql/src -c core ... Loaded symbols for /home/src/postgresql-devel/dev-build/src/test/regress/regress.so (gdb) bt #0 0xb76ecc7c in __kernel_vsyscall () #1 0xb7075577 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:56 #2 0xb7076cf3 in __GI_abort () at abort.c:89 #3 0x084c326a in ?? () #4 0x0a56c3b8 in ?? () #5 0xb76d232f in pg_atomic_init_u64 (ptr=0xbfa16fd4, val=0) at /home/src/postgresql-devel/postgresql-git/postgresql/src/include/port/atomics.h:445 #6 0xb76d50e4 in test_atomic_uint64 () at /home/src/postgresql-devel/postgresql-git/postgresql/src/test/regress/regress.c:1022 #7 0xb76d5756 in test_atomic_ops (fcinfo=0xa57c76c) at /home/src/postgresql-devel/postgresql-git/postgresql/src/test/regress/regress.c:1114 #8 0x0825bfee in ?? () ... Andres, have you had a chance to look at this? Nope, missed it somehow. On 32-bit x86, arch-x86.h leaves PG_HAVE_ATOMIC_U64_SUPPORT undefined. But generic-gcc.h, which is included later, then defines it. That's fine. The only reason arch-x86.h implements anything itself is that that allows older compilers than relying on intrinsics. But implementing 64bit atomics is too annoying by hand and isn't currently required. pg_atomic_init_u64 does AssertPointerAlignment(ptr, 8) on the variable, but there is no guarantee that it is 8-bytes aligned on x86. Hrmpf. Annoying. Gcc for a while claimed that was guaranteed, but, if I understood the tickets correctly, gave up on that. Unfortunately we have to rely (IIRC) on that for (quite old) x86s and some other architectures. It doesn't seem to be a problem on any native 64bit platform, because 64bit variables are 8byte aligned natively there. I think it can relatively easily be fixed by something like the attached. Don't have a pure 32bit environment to test though - the problem isn't reproducable in a 32bit chroot... Vladimir, if you apply that patch, do things work for you? Greetings, Andres Freund -- Using Opera's revolutionary email client: http://www.opera.com/mail/ -- 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] INSERT ... ON CONFLICT UPDATE and RLS
Peter, * Peter Geoghegan (p...@heroku.com) wrote: For column level privileges, you wouldn't expect to only get an error about not having the relevant update permissions at runtime, when the update path happens to be taken. And so it is for RLS. Right, that's the precedent we should be considering. Column-level privileges is a great example- you need both insert and update privileges for the columns involved for the command to succeed. It shouldn't depend on which path actually ends up being taken. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] POLA violation with \c service=
On Mon, Jan 05, 2015 at 02:26:59PM -0800, David Fetter wrote:
On Tue, Dec 30, 2014 at 04:48:11PM -0800, David Fetter wrote:
On Wed, Dec 17, 2014 at 08:14:04AM -0500, Andrew Dunstan wrote:
Yeah, that's the correct solution. It should not be terribly difficult to
create a test for a conninfo string in the dbname parameter. That's what
libpq does after all. We certainly don't want psql to have to try to
interpret the service file. psql just needs to let libpq do its work in
this
situation.
This took a little longer to get time to polish than I'd hoped, but
please find attached a patch which:
- Correctly connects to service= and postgres(ql)?:// with \c
- Disallows tab completion in the above cases
I'd like to see about having tab completion actually work correctly in
at least the service= case, but that's a matter for a follow-on patch.
Thanks to Andrew Dunstan for the original patch, and to Andrew Gierth
for his help getting it into shape.
Cheers,
David.
I should mention that the patch also corrects a problem where the
password was being saved/discarded at inappropriate times. Please
push this patch to the back branches :)
Per discussion with Stephen Frost, I've documented the previously
undocumented behavior with conninfo strings and URIs.
Cheers,
David.
--
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index bdfb67c..eb6a57b 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -796,18 +796,20 @@ testdb=gt;
/varlistentry
varlistentry
-termliteral\c/literal or literal\connect/literal literal[
replaceable class=parameterdbname/replaceable [ replaceable
class=parameterusername/replaceable ] [ replaceable
class=parameterhost/replaceable ] [ replaceable
class=parameterport/replaceable ] ]/literal/term
+termliteral\c/literal or literal\connect/literal literal [
{ [ replaceable class=parameterdbname/replaceable [ replaceable
class=parameterusername/replaceable ] [ replaceable
class=parameterhost/replaceable ] [ replaceable
class=parameterport/replaceable ] ] | replaceable
class=parameterconninfo/replaceable string | replaceable
class=parameterURI/replaceable } ] /literal/term
listitem
para
Establishes a new connection to a productnamePostgreSQL/
-server. If the new connection is successfully made, the
-previous connection is closed. If any of replaceable
+server using positional parameters as described below, a
+parameterconninfo/parameter string, or a acronymURI/acronym.
If the new connection is
+successfully made, the
+previous connection is closed. When using positional parameters, if
any of replaceable
class=parameterdbname/replaceable, replaceable
class=parameterusername/replaceable, replaceable
class=parameterhost/replaceable or replaceable
class=parameterport/replaceable are omitted or specified
as literal-/literal, the value of that parameter from the
-previous connection is used. If there is no previous
+previous connection is used. If using positional parameters and there
is no previous
connection, the applicationlibpq/application default for
the parameter's value is used.
/para
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 4ac21f2..f290fbc 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1610,6 +1610,8 @@ do_connect(char *dbname, char *user, char *host, char
*port)
PGconn *o_conn = pset.db,
*n_conn;
char *password = NULL;
+ boolkeep_password = true;
+ boolhas_connection_string = false;
if (!o_conn (!dbname || !user || !host || !port))
{
@@ -1623,14 +1625,32 @@ do_connect(char *dbname, char *user, char *host, char
*port)
return false;
}
- if (!dbname)
- dbname = PQdb(o_conn);
if (!user)
user = PQuser(o_conn);
+ else if (strcmp(user, PQuser(o_conn)) != 0)
+ keep_password = false;
+
if (!host)
host = PQhost(o_conn);
+ else if (strcmp(host, PQhost(o_conn)) != 0)
+ keep_password = false;
+
if (!port)
port = PQport(o_conn);
+ else if (strcmp(port, PQport(o_conn)) != 0)
+ keep_password = false;
+
+ has_connection_string = recognized_connection_string(dbname);
+
+ if (has_connection_string)
+ keep_password = false;
+
+ /*
+* Unlike the previous stanzas, changing only the
Re: [HACKERS] Turning recovery.conf into GUCs
On 01/08/2015 12:57 PM, Peter Eisentraut wrote: c) Infrastructure for changing settings effective during recovery. Right now we'd have to rebuild a lot of guc infrasturcture to allow that. It'd not be that hard to allow changing parameters like restore_command, primary_conninfo, recovery_target_* et al. That's for sure not the same commit, but once the infrastructure is in those won't be too hard. Right, if that worked, then it would be a real win. But this discussion is about right now, and the perspective of the user. That's rather a catch-22, isn't it? Last I checked, it was our policy to try to get smaller, more discrete patches rather than patches which try to change everything at once. -- 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] List of table names of a DB
Hi, pgpool-II (pgpool.net) does exactly the same thing. It receive SELECT query from clients, 1) parse it to find table names, and 2) gets the oids (unique identifier in the PostgreSQL system catalog) to recognize them. when the SELECT succeeds , it store the query result (cache) on either shared memory or memcached according to user's choice. For query cache invalidation, pgpool-II remembers all oids related to the SELECTs which are source of query cache. If one of tables get updated, pgpoool-II invalidates all of cache using the oid. For #1, pgpool-II has a query parser copied from PostgreSQL. pgpool-II is an open source project, so you could get some idea to implement your own tool. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp Sorry, it's not about querying. I am implementing an invalidation mechanism for Postgres Query Cache as part of my masters project. In order to this, I need to store details(like name) of each table the query uses. In essence, I need to store the table names of the cached queries. Initially, I thought of writing a code that could extract the table names but later discovered that it is a gargantuan task as I shall have to include around 600 production rules as was hinted in a Stackoverflow Exchange post. Hence, I thought of getting hold of the data structure used for storing table names of a DB but I couldn't get it. Sorry for the long post but do you know where these tables information of a DB gets stored? Or can you suggest me a mechanism(needn't be fully perfect) to extract the table names? I went through the parser of postgres but it was confusing. Thanks Date: Fri, 9 Jan 2015 00:46:30 +1300 Subject: Re: [HACKERS] List of table names of a DB From: dgrowle...@gmail.com To: in.live...@live.in CC: pgsql-hackers@postgresql.org On 8 January 2015 at 22:41, Deepak S in.live...@live.in wrote: Hello, I am unable to find the function which retrieves the 'list of names of the tables' used in a DB. You may find what you want in: select table_name from information_schema.tables; http://www.postgresql.org/docs/9.4/static/infoschema-tables.html Reason: I need a mechanism by which I can extract the names of the tables used in a query which has been parsed. My plan is to check for a match of each word in the query with a list of names of the tables used in the current DB so that each hit confirms a 'table name' in the query in most cases. This sounds rather flaky. Maybe it would be better to just EXPLAIN the query and see if you get error code 42P01postgres=# \set VERBOSITY verbosepostgres=# explain select * from doesnotexist;ERROR: 42P01: relation doesnotexist does not existLINE 1: explain select * from doesnotexist; Unfortunately this won't help you much if you want to know all of the tables that don't exist. Also, just for the future, a question like this might be more suited for the pgsql-gene...@postgresql.org list. Regards David Rowley -- 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] List of table names of a DB
Actually, code has moved to: https://github.com/snaga/pqc On 09/01/15 19:53, Mark Kirkwood wrote: Also see: https://code.google.com/p/pqc/ A project to implement a query cache using pgpool code, probably lots of good ideas there. Cheers Mark On 09/01/15 19:38, Tatsuo Ishii wrote: Hi, pgpool-II (pgpool.net) does exactly the same thing. It receive SELECT query from clients, 1) parse it to find table names, and 2) gets the oids (unique identifier in the PostgreSQL system catalog) to recognize them. when the SELECT succeeds , it store the query result (cache) on either shared memory or memcached according to user's choice. For query cache invalidation, pgpool-II remembers all oids related to the SELECTs which are source of query cache. If one of tables get updated, pgpoool-II invalidates all of cache using the oid. For #1, pgpool-II has a query parser copied from PostgreSQL. pgpool-II is an open source project, so you could get some idea to implement your own tool. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp Sorry, it's not about querying. I am implementing an invalidation mechanism for Postgres Query Cache as part of my masters project. In order to this, I need to store details(like name) of each table the query uses. In essence, I need to store the table names of the cached queries. Initially, I thought of writing a code that could extract the table names but later discovered that it is a gargantuan task as I shall have to include around 600 production rules as was hinted in a Stackoverflow Exchange post. Hence, I thought of getting hold of the data structure used for storing table names of a DB but I couldn't get it. Sorry for the long post but do you know where these tables information of a DB gets stored? Or can you suggest me a mechanism(needn't be fully perfect) to extract the table names? I went through the parser of postgres but it was confusing. Thanks Date: Fri, 9 Jan 2015 00:46:30 +1300 Subject: Re: [HACKERS] List of table names of a DB From: dgrowle...@gmail.com To: in.live...@live.in CC: pgsql-hackers@postgresql.org On 8 January 2015 at 22:41, Deepak S in.live...@live.in wrote: Hello, I am unable to find the function which retrieves the 'list of names of the tables' used in a DB. You may find what you want in: select table_name from information_schema.tables; http://www.postgresql.org/docs/9.4/static/infoschema-tables.html Reason: I need a mechanism by which I can extract the names of the tables used in a query which has been parsed. My plan is to check for a match of each word in the query with a list of names of the tables used in the current DB so that each hit confirms a 'table name' in the query in most cases. This sounds rather flaky. Maybe it would be better to just EXPLAIN the query and see if you get error code 42P01postgres=# \set VERBOSITY verbosepostgres=# explain select * from doesnotexist;ERROR: 42P01: relation doesnotexist does not existLINE 1: explain select * from doesnotexist; Unfortunately this won't help you much if you want to know all of the tables that don't exist. Also, just for the future, a question like this might be more suited for the pgsql-gene...@postgresql.org list. Regards David Rowley -- 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_rewind in contrib
On Fri, Jan 9, 2015 at 1:02 AM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
Fixed all the errors I got on MSVC. The biggest change was rewriting the
code that determines if a file is a relation file, based on its filename. It
used a regular expression, which I replaced with a bunch of sscanf calls,
and a cross-check that GetRelationPath() returns the same filename.
It looks definitely better like that. Thanks.
copy_fetch.c: In function 'check_samefile':
copy_fetch.c:298:2: warning: passing argument 2 of '_fstat64i32' from
incompatib
le pointer type [enabled by default]
if (fstat(fd1, statbuf1) 0)
^
In file included from ../../src/include/port.h:283:0,
from ../../src/include/c.h:1050,
from ../../src/include/postgres_fe.h:25,
from copy_fetch.c:10:
c:\mingw\include\sys\stat.h:200:32: note: expected 'struct _stat64i32 *'
but arg
ument is of type 'struct stat *'
__CRT_MAYBE_INLINE int __cdecl _fstat64i32(int desc, struct _stat64i32
*_stat)
{
Strange. There isn't anything special about the fstat() calls in pg_rewind.
Do you get these from other modules that call fstat, e.g.
pg_stat_statements?
I did not see these warnings when building with MSVC, and don't have MinGW
installed currently.
Don't worry about those ones, it is discussed here already:
http://www.postgresql.org/message-id/CAB7nPqTrmmZo2y92DfZEd-mWo1cenEoaUhCZppv=ob84-4c...@mail.gmail.com
MSVC build still has a warning:
C:\Users\mpaquier\git\postgres\pg_rewind.vcxproj (default target) (60) -
(Link target) -
xlogreader.obj : warning LNK4049: locally defined symbol
pg_crc32c_table imported
[C:\Users\ioltas\git\postgres\pg_rewind.vcxproj]
The documentation needs some more polishing:
1) s/pg_reind/pg_rewind
2) by settings wal_log_hints = on = by setting
varnamewal_log_hints/ to literalon/
3) You should avoid using an hardcoded list of items in a block para
to list how pg_rewind works. Each item in the list should be changed
to use orderedlist:
para
The basic idea is to copy everything from the blah...
orderedlist
listitem
para
Scan the WAL log of blah..
/para
/listitem
listitem
para
paragraph2
/para
listitem
[blah.]
/orderedlist
/para
4) --source-server and --target-pgdata are not listed in the list of options.
5) The synopsis should be written like that IMO: pg_rewind [option...]
6) pg_rewind is listed several times, doesn't it need application?
7) pg_xlog should use filename
8) Perhaps a section See also at the bottom could be added to
mention pg_basebackup?
Regards,
--
Michael
--
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] Transactions involving multiple postgres foreign servers
On Thu, Jan 8, 2015 at 8:24 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jan 7, 2015 at 11:20 AM, Kevin Grittner kgri...@ymail.com wrote: If we are going to include a distributed transaction manager with PostgreSQL, it *must* persist enough information about the transaction ID and where it is used in a way that will survive a subsequent crash before beginning the PREPARE on any of the systems. After all nodes are PREPAREd it must flag that persisted data to indicate that it is now at a point where ROLLBACK is no longer an option. Only then can it start committing the prepared transactions. After the last node is committed it can clear this information. On start-up the distributed transaction manager must check for any distributed transactions left in progress and commit or rollback based on the preceding; doing retries indefinitely until it succeeds or is told to stop. I think one key question here is whether all of this should be handled in PostgreSQL core or whether some of it should be handled in other ways. Is the goal to make postgres_fdw (and FDWs for other databases that support 2PC) to persist enough information that someone *could* write a transaction manager for PostgreSQL, or is the goal to actually write that transaction manager? Just figuring out how to persist the necessary information is a non-trivial problem by itself. You might think that you could just insert a row into a local table saying, hey, I'm about to prepare a transaction remotely, but of course that doesn't work: if you then go on to PREPARE before writing and flushing the local commit record, then a crash before that's done leaves a dangling prepared transaction on the remote note. You might think to write the record, then after writing and flush the local commit record do the PREPARE. But you can't do that either, because now if the PREPARE fails you've already committed locally. I guess what you need to do is something like: 1. Write and flush a WAL record indicating an intent to prepare, with a list of foreign server OIDs and GUIDs. 2. Prepare the remote transaction on each node. If any of those operations fail, roll back any prepared nodes and error out. 3. Commit locally (i.e. RecordTransactionCommit, writing and flushing WAL). 4. Try to commit the remote transactions. 5. Write a WAL record indicating that you committed the remote transactions OK. If you fail after step 1, you can straighten things out by looking at the status of the transaction: if the transaction committed, any transactions we intended-to-prepare need to be checked. If they are still prepared, we need to commit them or roll them back according to what happened to our XID. When you want to strengthen and commit things, the foreign server may not be available to do that. As Kevin pointed out in above, we need to keep on retrying to resolve (commit or rollback based on the status of local transaction) the PREPAREd transactions on foreign server till they are resolved. So, we will have to persist the information somewhere else than the WAL OR keep on persisting the WALs even after the corresponding local transaction has been committed or aborted, which I don't think is a good idea, since that will have impact on replication, VACUUM esp. because it's going to affect the oldest transaction in WAL. That's where Andres's suggestion might help. (Andres is talking in my other ear suggesting that we ought to reuse the 2PC infrastructure to do all this. I'm not convinced that's a good idea, but I'll let him present his own ideas here if he wants to rather than trying to explain them myself.) We can persist the information about distributed transaction (which esp. require 2PC) similar to the way as 2PC infrastructure in pg_twophase directory. I am still investigating whether we can re-use existing 2PC infrastructure or not. My initial reaction is no, since 2PC persists information about local transaction including locked objects, WALs (?) in pg_twophase directory, which is not required for a distributed transaction. But rest of the mechanism like the manner of processing the records during normal processing and recovery looks very useful. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
Re: [HACKERS] List of table names of a DB
Sorry, it's not about querying. I am implementing an invalidation mechanism for Postgres Query Cache as part of my masters project. In order to this, I need to store details(like name) of each table the query uses. In essence, I need to store the table names of the cached queries. Initially, I thought of writing a code that could extract the table names but later discovered that it is a gargantuan task as I shall have to include around 600 production rules as was hinted in a Stackoverflow Exchange post. Hence, I thought of getting hold of the data structure used for storing table names of a DB but I couldn't get it. Sorry for the long post but do you know where these tables information of a DB gets stored? Or can you suggest me a mechanism(needn't be fully perfect) to extract the table names? I went through the parser of postgres but it was confusing. Thanks Date: Fri, 9 Jan 2015 00:46:30 +1300 Subject: Re: [HACKERS] List of table names of a DB From: dgrowle...@gmail.com To: in.live...@live.in CC: pgsql-hackers@postgresql.org On 8 January 2015 at 22:41, Deepak S in.live...@live.in wrote: Hello, I am unable to find the function which retrieves the 'list of names of the tables' used in a DB. You may find what you want in: select table_name from information_schema.tables; http://www.postgresql.org/docs/9.4/static/infoschema-tables.html Reason: I need a mechanism by which I can extract the names of the tables used in a query which has been parsed. My plan is to check for a match of each word in the query with a list of names of the tables used in the current DB so that each hit confirms a 'table name' in the query in most cases. This sounds rather flaky. Maybe it would be better to just EXPLAIN the query and see if you get error code 42P01postgres=# \set VERBOSITY verbosepostgres=# explain select * from doesnotexist;ERROR: 42P01: relation doesnotexist does not existLINE 1: explain select * from doesnotexist; Unfortunately this won't help you much if you want to know all of the tables that don't exist. Also, just for the future, a question like this might be more suited for the pgsql-gene...@postgresql.org list. Regards David Rowley
Re: [HACKERS] Transactions involving multiple postgres foreign servers
On Thu, Jan 8, 2015 at 7:02 PM, Kevin Grittner kgri...@ymail.com wrote: Ashutosh Bapat ashutosh.ba...@enterprisedb.com wrote: On Wed, Jan 7, 2015 at 9:50 PM, Kevin Grittner kgri...@ymail.com wrote: Also, as previously mentioned, it must behave in some reasonable way if a database is not configured to support 2PC, especially since 2PC is off by default in PostgreSQL. We can have a per foreign server option, which says whether the corresponding server is able to participate in 2PC. A transaction spanning multiple foreign server with at least one of them not capable of participating in 2PC will be aborted. Will that work? In case a user flags a foreign server as capable to 2PC incorrectly, I expect the corresponding FDW would raise error (either because PREPARE fails or FDW doesn't handle that case) and the transaction will be aborted anyway. That sounds like one way to handle it. I'm not clear on how you plan to determine whether 2PC is required for a transaction. (Apologies if it was previously mentioned and I've forgotten it.) Any transaction involving more than one server (including local one, I guess), will require two PC. A transaction may modify and access remote database but not local one. In such a case, the state of local transaction doesn't matter once the remote transaction is committed or rolled back. I don't mean to suggest that these problems are insurmountable; I just think that people often underestimate the difficulty of writing a distributed transaction manager and don't always recognize the problems that it will cause if all of the failure modes are not considered and handled. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
Re: [HACKERS] Compression of full-page-writes
On Thu, Jan 8, 2015 at 11:59 PM, Rahila Syed rahilasyed...@gmail.com wrote: Below are performance numbers in case of synchronous replication with and without fpw compression using latest version of patch(version 14). The patch helps improve performance considerably. Both master and standby are on the same machine in order to get numbers independent of network overhead. So this test can be used to evaluate how shorter records influence performance since the master waits for flush confirmation from the standby, right? The compression patch helps to increase tps by 10% . It also helps reduce I/O to disk , latency and total runtime for a fixed number of transactions as shown below. The compression of WAL is quite high around 40%. Compressionon off WAL generated 23037180520(~23.04MB) 38196743704(~38.20MB) Isn't that GB and not MB? TPS 264.18239.34 Latency average60.541 ms 66.822 ms Latency stddev 126.567 ms 130.434 ms Total writes to disk 145045.310 MB 192357.250MB Runtime 15141.0 s 16712.0 s How many FPWs have been generated and how many dirty buffers have been flushed for the 3 checkpoints of each test? Any data about the CPU activity? -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Comment typo in src/backend/executor/execMain.c
Hi, I ran into a comment type. Please find attached a patch. Best regards, Etsuro Fujita diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 8c799d3..28abfa4 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -2024,7 +2024,7 @@ EvalPlanQualFetch(EState *estate, Relation relation, int lockmode, * heap_lock_tuple() will throw an error, and so would any later * attempt to update or delete the tuple. (We need not check cmax * because HeapTupleSatisfiesDirty will consider a tuple deleted - * by our transaction dead, regardless of cmax.) Wee just checked + * by our transaction dead, regardless of cmax.) We just checked * that priorXmax == xmin, so we can test that variable instead of * doing HeapTupleHeaderGetXmin again. */ -- 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] orangutan seizes up during isolation-check
On Wed, Dec 31, 2014 at 01:52:49PM -0500, Noah Misch wrote: On Sun, Dec 28, 2014 at 07:20:04PM -0500, Andrew Dunstan wrote: On 12/28/2014 04:58 PM, Noah Misch wrote: The gettext maintainer was open to implementing the setlocale_native_forked() technique in gettext, though the last visible progress was in October. In any event, PostgreSQL builds will see older gettext for several years. If setlocale-darwin-fork-v1.patch is not wanted, I suggest making the postmaster check during startup whether it has become multithreaded. If multithreaded: FATAL: postmaster became multithreaded during startup HINT: Set the LC_ALL environment variable to a valid locale. I would like to go ahead and commit setlocale-main-harden-v1.patch, which is a good thing to have regardless of what happens with gettext. I'm OK with this, but on its own it won't fix orangutan's problems, will it? Right; setlocale-main-harden-v1.patch fixes a bug not affecting orangutan at all. None of the above will make orangutan turn green. Checking multithreading during startup would merely let it fail cleanly. OS X --enable-nls buildfarm members should run tests under LANG=C instead of with locale environment variables unset (make check NO_LOCALE=1). I see two ways to arrange that: (1) add a build-farm.conf option, or (2) have pg_regress.c:initialize_environment() treat OS X like Windows. I mildly favor (2); see attached, untested patch. Windows and OS X --enable-nls share the characteristic that setlocale(LC_x, ) consults sources other than environment variables. (I do wonder why commit 4a6fd46 used LANG=en instead of LANG=C.) On the other hand, LANG=en has been inessential on Windows ever since pg_regress --no-locale started to use initdb --no-locale. While I prefer to see the LANG= hack go away rather than proliferate, I can't cite a practical reason to care. Thanks, nm diff --git a/src/test/regress/pg_regress.c b/src/test/regress/pg_regress.c index e8c644b..e55835e 100644 --- a/src/test/regress/pg_regress.c +++ b/src/test/regress/pg_regress.c @@ -790,9 +790,16 @@ initialize_environment(void) unsetenv(LC_NUMERIC); unsetenv(LC_TIME); unsetenv(LANG); - /* On Windows the default locale cannot be English, so force it */ -#if defined(WIN32) || defined(__CYGWIN__) - putenv(LANG=en); + /* +* Most platforms have adopted the POSIX locale as their +* implementation-defined default locale. Exceptions include native +* Windows, Darwin with --enable-nls, and Cygwin with --enable-nls. +* (Use of --enable-nls matters because libintl replaces setlocale().) +* Also, PostgreSQL does not support Darwin with locale environment +* variables unset; see PostmasterMain(). +*/ +#if defined(WIN32) || defined(__CYGWIN__) || defined(__darwin__) + putenv(LANG=C); #endif } -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] server_version_num should be GUC_REPORT
Hi all
While looking into client code that relies on parsing server_version
instead of checking server_version_num, I was surprised to discover that
server_version_num isn't sent to the client by the server as part of the
standard set of parameters reported post-auth.
The docs reflect this:
http://www.postgresql.org/docs/current/static/protocol-flow.html#PROTOCOL-ASYNC
The attached patch marks server_version_num GUC_REPORT and documents
that it's reported to the client automatically.
(See mention here:
https://github.com/impossibl/pgjdbc-ng/commit/40b6dc658a9b38725be220e2fa653a5a8a0cbae4#commitcomment-8427979)
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
From abb5db65094710155d723f2848f21d61d25e6994 Mon Sep 17 00:00:00 2001
From: Craig Ringer cr...@2ndquadrant.com
Date: Fri, 9 Jan 2015 13:54:26 +0800
Subject: [PATCH] Send server_version_num to client on start
Add server_version_num to the list of parameters the server
automatically sends the client on startup.
Previously we sent ParameterStatus messages for server_version and
various other parameters, but didn't include server_version_num. Since
we're trying to discourage clients from parsing the version number
it'd make sense to actually send it to them when they need it.
---
doc/src/sgml/protocol.sgml | 4 +++-
src/backend/utils/misc/guc.c | 2 +-
2 files changed, 4 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 31bbc0d..cbeedc0 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -1091,6 +1091,7 @@
At present there is a hard-wired set of parameters for which
ParameterStatus will be generated: they are
varnameserver_version/,
+varnameserver_version_num/,
varnameserver_encoding/,
varnameclient_encoding/,
varnameapplication_name/,
@@ -1106,7 +1107,8 @@
varnamestandard_conforming_strings/ was not reported by releases
before 8.1;
varnameIntervalStyle/ was not reported by releases before 8.4;
-varnameapplication_name/ was not reported by releases before 9.0.)
+varnameapplication_name/ was not reported by releases before 9.0;
+varnameserver_version_num/ was not reported by releases before 9.5)
Note that
varnameserver_version/,
varnameserver_encoding/ and
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index d4a77ea..a51e511 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2510,7 +2510,7 @@ static struct config_int ConfigureNamesInt[] =
{server_version_num, PGC_INTERNAL, PRESET_OPTIONS,
gettext_noop(Shows the server version as an integer.),
NULL,
- GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+ GUC_REPORT | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
},
server_version_num,
PG_VERSION_NUM, PG_VERSION_NUM, PG_VERSION_NUM,
--
2.1.0
--
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] List of table names of a DB
Also see: https://code.google.com/p/pqc/ A project to implement a query cache using pgpool code, probably lots of good ideas there. Cheers Mark On 09/01/15 19:38, Tatsuo Ishii wrote: Hi, pgpool-II (pgpool.net) does exactly the same thing. It receive SELECT query from clients, 1) parse it to find table names, and 2) gets the oids (unique identifier in the PostgreSQL system catalog) to recognize them. when the SELECT succeeds , it store the query result (cache) on either shared memory or memcached according to user's choice. For query cache invalidation, pgpool-II remembers all oids related to the SELECTs which are source of query cache. If one of tables get updated, pgpoool-II invalidates all of cache using the oid. For #1, pgpool-II has a query parser copied from PostgreSQL. pgpool-II is an open source project, so you could get some idea to implement your own tool. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp Sorry, it's not about querying. I am implementing an invalidation mechanism for Postgres Query Cache as part of my masters project. In order to this, I need to store details(like name) of each table the query uses. In essence, I need to store the table names of the cached queries. Initially, I thought of writing a code that could extract the table names but later discovered that it is a gargantuan task as I shall have to include around 600 production rules as was hinted in a Stackoverflow Exchange post. Hence, I thought of getting hold of the data structure used for storing table names of a DB but I couldn't get it. Sorry for the long post but do you know where these tables information of a DB gets stored? Or can you suggest me a mechanism(needn't be fully perfect) to extract the table names? I went through the parser of postgres but it was confusing. Thanks Date: Fri, 9 Jan 2015 00:46:30 +1300 Subject: Re: [HACKERS] List of table names of a DB From: dgrowle...@gmail.com To: in.live...@live.in CC: pgsql-hackers@postgresql.org On 8 January 2015 at 22:41, Deepak S in.live...@live.in wrote: Hello, I am unable to find the function which retrieves the 'list of names of the tables' used in a DB. You may find what you want in: select table_name from information_schema.tables; http://www.postgresql.org/docs/9.4/static/infoschema-tables.html Reason: I need a mechanism by which I can extract the names of the tables used in a query which has been parsed. My plan is to check for a match of each word in the query with a list of names of the tables used in the current DB so that each hit confirms a 'table name' in the query in most cases. This sounds rather flaky. Maybe it would be better to just EXPLAIN the query and see if you get error code 42P01postgres=# \set VERBOSITY verbosepostgres=# explain select * from doesnotexist;ERROR: 42P01: relation doesnotexist does not existLINE 1: explain select * from doesnotexist; Unfortunately this won't help you much if you want to know all of the tables that don't exist. Also, just for the future, a question like this might be more suited for the pgsql-gene...@postgresql.org list. Regards David Rowley -- 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] List of table names of a DB
On Fri, Jan 9, 2015 at 7:14 AM, Deepak S in.live...@live.in wrote: Sorry, it's not about querying. I am implementing an invalidation mechanism for Postgres Query Cache as part of my masters project. In order to this, I need to store details(like name) of each table the query uses. In essence, I need to store the table names of the cached queries. Initially, I thought of writing a code that could extract the table names but later discovered that it is a gargantuan task as I shall have to include around 600 production rules as was hinted in a Stackoverflow Exchange post. Hence, I thought of getting hold of the data structure used for storing table names of a DB but I couldn't get it. For prototyping you might also find https://github.com/pganalyze/pg_query useful. Its a Ruby-based library that makes the Postgres parser easier to access from the outside, getting a list of tables from a query is trivial - but if you need the oids you'll have to do it like pgpool does. (feel free to ping me off-list about this) Best, -- Lukas Fittl Skype: lfittl Phone: +43 6991 2770651
Re: [HACKERS] PATCH: decreasing memory needlessly consumed by array_agg
Hi,
On 8.1.2015 08:53, Ali Akbar wrote:
In the CF, the status becomes Needs Review. Let's continue our
discussion of makeArrayResult* behavior if subcontext=false and
release=true (more below):
2014-12-22 8:08 GMT+07:00 Ali Akbar the.ap...@gmail.com
mailto:the.ap...@gmail.com:
With this API, i think we should make it clear if we call
initArrayResult with subcontext=false, we can't call
makeArrayResult, but we must use makeMdArrayResult directly.
Or better, we can modify makeArrayResult to release according to
astate-private_cxt:
@@ -4742,7 +4742,7 @@ makeArrayResult(ArrayBuildState *astate,
dims[0] = astate-nelems;
lbs[0] = 1;
- return makeMdArrayResult(astate, ndims, dims, lbs, rcontext,
true);
+ return makeMdArrayResult(astate, ndims, dims, lbs, rcontext,
astate-private_cxt);
I've done this, so makeArrayResult() uses the private_cxt flag.
Or else we implement what you suggest below (more comments below):
Thinking about the 'release' flag a bit more - maybe we could do
this
instead:
if (release astate-private_cxt)
MemoryContextDelete(astate-mcontext);
else if (release)
{
pfree(astate-dvalues);
pfree(astate-dnulls);
pfree(astate);
}
i.e. either destroy the whole context if possible, and just free the
memory when using a shared memory context. But I'm afraid this would
penalize the shared memory context, because that's intended for
cases where all the build states coexist in parallel and then at some
point are all converted into a result and thrown away. Adding pfree()
calls is no improvement here, and just wastes cycles.
As per Tom's comment, i'm using parent memory context instead of
shared memory context below.
In the future, if some code writer decided to use subcontext=false,
to save memory in cases where there are many array accumulation, and
the parent memory context is long-living, current code can cause
memory leak. So i think we should implement your suggestion
(pfreeing astate), and warn the implication in the API comment. The
API user must choose between release=true, wasting cycles but
preventing memory leak, or managing memory from the parent memory
context.
I'm wondering whether this is necessary after fixing makeArrayResult to
use the privat_cxt flag. It's still possible to call makeMdArrayResult
directly (with the wrong 'release' value).
Another option might be to get rid of the 'release' flag altogether, and
just use the 'private_cxt' - I'm not aware of a code using release=false
with private_cxt=true (e.g. to build the same array twice from the same
astate). But maybe there's such code, and another downside is that it'd
break the existing API.
In one possible use case, for efficiency maybe the caller will
create a special parent memory context for all array accumulation.
Then uses makeArrayResult* with release=false, and in the end
releasing the parent memory context once for all.
Yeah, although I'd much rather not break the existing code at all. That
is - my goal is not to make it slower unless absolutely necessary (and
in that case the code may be fixed per your suggestion). But I'm not
convinced it's worth it.
As for the v6 patch:
- the patch applies cleanly to master
- make check is successfull
- memory benefit is still there
- performance benefit i think is negligible
Reviewing the code, found this:
@@ -573,7 +578,22 @@ array_agg_array_transfn(PG_FUNCTION_ARGS)
elog(ERROR, array_agg_array_transfn called in
non-aggregate context);
}
-state = PG_ARGISNULL(0) ? NULL : (ArrayBuildStateArr *)
PG_GETARG_POINTER(0);
+
+if (PG_ARGISNULL(0))
+{
+Oidelement_type = get_element_type(arg1_typeid);
+
+if (!OidIsValid(element_type))
+ereport(ERROR,
+(errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg(data type %s is not an array type,
+format_type_be(arg1_typeid;
digging more, it looks like those code required because
accumArrayResultArr checks the element type:
/* First time through --- initialize */
Oidelement_type = get_element_type(array_type);
if (!OidIsValid(element_type))
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg(data type %s is not an array type,
format_type_be(array_type;
astate = initArrayResultArr(array_type, element_type,
rcontext, true);
I think
Re: [HACKERS] List of table names of a DB
On 8 January 2015 at 22:41, Deepak S in.live...@live.in wrote: Hello, I am unable to find the function which retrieves the 'list of names of the tables' used in a DB. You may find what you want in: select table_name from information_schema.tables; http://www.postgresql.org/docs/9.4/static/infoschema-tables.html Reason: I need a mechanism by which I can extract the names of the tables used in a query which has been parsed. My plan is to check for a match of each word in the query with a list of names of the tables used in the current DB so that each hit confirms a 'table name' in the query in most cases. This sounds rather flaky. Maybe it would be better to just EXPLAIN the query and see if you get error code 42P01 postgres=# \set VERBOSITY verbose postgres=# explain select * from doesnotexist; ERROR: 42P01: relation doesnotexist does not exist LINE 1: explain select * from doesnotexist; Unfortunately this won't help you much if you want to know all of the tables that don't exist. Also, just for the future, a question like this might be more suited for the pgsql-gene...@postgresql.org list. Regards David Rowley
Re: [HACKERS] Proposal: Log inability to lock pages during vacuum
On 2015-01-04 01:53:24 +0100, Andres Freund wrote: Ah, interesting, I didn't remember we had that. I guess one possible tweak is to discount the pages we skip from pinned_pages; or we could keep a separate count of pages waited for. Jim, up for a patch? This is still wrong. I think just counting skipped pages, without distinct messages for waiting/not waiting, is good enough for now. Everything else would only be actually meaningful if we actually tracked the waiting time. Pushed a commit for this, with additional improvements to autovacuum's log output from: LOG: automatic vacuum of table postgres.public.frak: index scans: 0 pages: 0 removed, 1672 remain skipped 1 pages due to buffer pins tuples: 0 removed, 309959 remain, 309774 are dead but not yet removable buffer usage: 4258 hits, 0 misses, 0 dirtied avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s system usage: CPU 0.00s/0.04u sec elapsed 0.46 sec to: LOG: automatic vacuum of table postgres.public.frak: index scans: 0 pages: 0 removed, 1672 remain, 1 skipped due to pins tuples: 0 removed, 309959 remain, 309774 are dead but not yet removable buffer usage: 4258 hits, 0 misses, 0 dirtied avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s system usage: CPU 0.00s/0.04u sec elapsed 0.46 sec as the 'skipped ...' line didn't really look in line with the rest. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Parallel Seq Scan
On Mon, Jan 5, 2015 at 8:31 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Jan 2, 2015 at 5:36 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Thu, Jan 1, 2015 at 11:29 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Jan 1, 2015 at 12:00 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: Can we check the number of free bgworkers slots to set the max workers? The real solution here is that this patch can't throw an error if it's unable to obtain the desired number of background workers. It needs to be able to smoothly degrade to a smaller number of background workers, or none at all. I think handling this way can have one side effect which is that if we degrade to smaller number, then the cost of plan (which was decided by optimizer based on number of parallel workers) could be more than non-parallel scan. Ideally before finalizing the parallel plan we should reserve the bgworkers required to execute that plan, but I think as of now we can workout a solution without it. I don't think this is very practical. When cached plans are in use, we can have a bunch of plans sitting around that may or may not get reused at some point in the future, possibly far in the future. The current situation, which I think we want to maintain, is that such plans hold no execution-time resources (e.g. locks) and, generally, don't interfere with other things people might want to execute on the system. Nailing down a bunch of background workers just in case we might want to use them in the future would be pretty unfriendly. I think it's right to view this in the same way we view work_mem. We plan on the assumption that an amount of memory equal to work_mem will be available at execution time, without actually reserving it. Are we sure that in such cases we will consume work_mem during execution? In cases of parallel_workers we are sure to an extent that if we reserve the workers then we will use it during execution. Nonetheless, I have proceded and integrated the parallel_seq scan patch with v0.3 of parallel_mode patch posted by you at below link: http://www.postgresql.org/message-id/CA+TgmoYmp_=xcjehvjzt9p8drbgw-pdpjhxbhza79+m4o-c...@mail.gmail.com Few things to note about this integrated patch are: 1. In this new patch, I have just integrated it with Robert's parallel_mode patch and not done any further development or fixed known things like changes in optimizer, prepare queries, etc. You might notice that new patch has lesser size as compare to previous patch and the reason is that there were some duplicate stuff between previous version of parallel_seqscan patch and parallel_mode which I have eliminated. 2. To enable two types of shared memory queue's (error queue and tuple queue), we need to ensure that we switch to appropriate queue during communication of various messages from parallel worker to master backend. There are two ways to do it a. Save the information about error queue during startup of parallel worker (ParallelMain()) and then during error, set the same (switch to error queue in errstart() and switch back to tuple queue in errfinish() and errstart() in case errstart() doesn't need to propagate error). b. Do something similar as (a) for tuple queue in printtup or other place if any for non-error messages. I think approach (a) is slightly better as compare to approach (b) as we need to switch many times for tuple queue (for each tuple) and there could be multiple places where we need to do the same. For now, I have used approach (a) in Patch which needs some more work if we agree on the same. 3. As per current implementation of Parallel_seqscan, it needs to use some information from parallel.c which was not exposed, so I have exposed the same by moving it to parallel.h. Information that is required is as follows: ParallelWorkerNumber, FixedParallelState and shm keys - This is used to decide the blocks that needs to be scanned. We might change it in future the way parallel scan/work distribution is done, but I don't see any harm in exposing this information. 4. Sending ReadyForQuery If the plan happens to need that amount of memory and if it actually isn't available when needed, then performance will suck; conceivably, the OOM killer might trigger. But it's the user's job to avoid this by not setting work_mem too high in the first place. Whether this system is for the best is arguable: one can certainly imagine a system where, if there's not enough memory at execution time, we consider alternatives like (a) replanning with a lower memory target, (b) waiting until more memory is available, or (c) failing outright in lieu of driving the machine into swap. But devising such a system is complicated -- for example, replanning with a lower memory target might be latch onto a far more expensive plan, such that we would have been better off waiting for more
Re: [HACKERS] Parallel Seq Scan
On Thu, Jan 8, 2015 at 5:12 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Mon, Jan 5, 2015 at 8:31 PM, Robert Haas robertmh...@gmail.com wrote: Sorry for incomplete mail sent prior to this, I just hit the send button by mistake. 4. Sending ReadyForQuery() after completely sending the tuples, as that is required to know that all the tuples are received and I think we should send the same on tuple queue rather than on error queue. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com parallel_seqscan_v3.patch Description: Binary data -- 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] parallel mode and parallel contexts
On Wed, Jan 7, 2015 at 11:03 PM, Robert Haas robertmh...@gmail.com wrote:
I have little doubt that this version is still afflicted with various
bugs, and the heavyweight locking issue remains to be dealt with, but
on the whole I think this is headed in the right direction.
+ParallelMain(Datum main_arg)
{
..
+ /*
+ * Now that we have a resource owner, we can attach to the dynamic
+ * shared memory
segment and read the table of contents.
+ */
+ seg = dsm_attach(DatumGetInt32(main_arg));
Here, I think DatumGetUInt32() needs to be used instead of
DatumGetInt32() as the segment handle is uint32.
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] Possible typo in create_policy.sgml
On 8 January 2015 at 08:30, Dean Rasheed dean.a.rash...@gmail.com wrote: I have a wider concern about the wording on this page - both the rewritten paragraph and elsewhere talk about policies in terms of limiting access to or filtering out rows. However, since policy expressions are OR'ed together and there is a default-deny policy when RLS is enabled, I think it should be talking about policies in terms of permitting access to tables that have row security enabled. [There's also a typo further down -- filter out the records which are visible, should be not visible] What do you think of the attached rewording? Regards, Dean diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml new file mode 100644 index 8ef8556..066aa76 *** a/doc/src/sgml/ref/create_policy.sgml --- b/doc/src/sgml/ref/create_policy.sgml *** CREATE POLICY replaceable class=parame *** 39,56 /para para !A policy limits the ability to SELECT, INSERT, UPDATE, or DELETE rows !in a table to those rows which match the relevant policy expression. !Existing table rows are checked against the expression specified via !USING, while new rows that would be created via INSERT or UPDATE are !checked against the expression specified via WITH CHECK. Generally, !the system will enforce filter conditions imposed using security !policies prior to qualifications that appear in the query itself, in !order to the prevent the inadvertent exposure of the protected data to !user-defined functions which might not be trustworthy. However, !functions and operators marked by the system (or the system !administrator) as LEAKPROOF may be evaluated before policy !expressions, as they are assumed to be trustworthy. /para para --- 39,56 /para para !A policy permits SELECT, INSERT, UPDATE or DELETE commands to access rows !in a table that has row level security enabled. Access to existing table !rows is granted if they match a policy expression specified via USING, !while new rows that would be created via INSERT or UPDATE are checked !against policy expressions specified via WITH CHECK. For policy !expressions specified via USING which grant access to existing rows, the !system will generally test the policy expressions prior to any !qualifications that appear in the query itself, in order to the prevent the !inadvertent exposure of the protected data to user-defined functions which !might not be trustworthy. However, functions and operators marked by the !system (or the system administrator) as LEAKPROOF may be evaluated before !policy expressions, as they are assumed to be trustworthy. /para para *** CREATE POLICY replaceable class=parame *** 154,160 Any acronymSQL/acronym conditional expression (returning typeboolean/type). The conditional expression cannot contain any aggregate or window functions. This expression will be added ! to queries to filter out the records which are visible to the query. /para /listitem /varlistentry --- 154,161 Any acronymSQL/acronym conditional expression (returning typeboolean/type). The conditional expression cannot contain any aggregate or window functions. This expression will be added ! to queries that refer to the table if row level security is enabled, ! and will allow access to rows matching the expression. /para /listitem /varlistentry *** CREATE POLICY replaceable class=parame *** 164,174 listitem para Any acronymSQL/acronym conditional expression (returning ! typeboolean/type). The condition expression cannot contain ! any aggregate or window functions. This expression will be added ! to queries which are attempting to add records to the table as ! with-check options, and an error will be thrown if this condition ! returns false for any records being added. /para /listitem /varlistentry --- 165,176 listitem para Any acronymSQL/acronym conditional expression (returning ! typeboolean/type). The conditional expression cannot contain ! any aggregate or window functions. This expression will be used in ! commandINSERT/command and commandUPDATE/command queries on ! the table if row level security is enabled, and an error will be thrown ! if the expression evaluates to false for any of the new records added or ! updated. /para /listitem /varlistentry -- 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] The return value of allocate_recordbuf()
Hi, On 2015-01-05 14:18:35 +0900, Michael Paquier wrote: Note that for 9.4, I think that we should complain about an OOM in logical.c where malloc is used as now process would simply crash if NULL is returned by XLogReaderAllocate. That's the object of the second patch. Yes, that's clearly an oversight... ctx-reader = XLogReaderAllocate(read_page, ctx); + if (!ctx-reader) + ereport(ERROR, + (errcode(ERRCODE_OUT_OF_MEMORY), + errmsg(out of memory), + errdetail(Failed while allocating an XLog reading processor.))); + I've removed the errdetail() as a) its content is quite confusing b) we don't add error details that don't add more information than the function name already does as it's implicitly included in the logging. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Escaping from blocked send() reprised.
On 2014-10-03 16:26:35 +0200, Andres Freund wrote: On 2014-10-03 17:12:18 +0300, Heikki Linnakangas wrote: 0002 now makes sense on its own and doesn't change anything around the interrupt handling. Oh, and it compiles without 0003. WaitLatchOrSocket() can throw an error, so it's not totally safe to call that underneath OpenSSL. Hm. Fair point. I think we should fix this by simply prohibiting WaitLatch/WaitLatchOrSocket from ERRORing out. The easiest, and imo acceptable, thing is to simply convert the relevant ERRORs to FATAL. I think that'd be perfectly fine as it seems very unlikely that we continue sanely afterwards. It would really be nice if we had a simple way to raise a FATAL that won't go to the client for situations like this. I'd proposed elog(FATAL | COMERROR, ...) in the past... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Possible typo in create_policy.sgml
On 6 January 2015 at 19:25, Stephen Frost sfr...@snowman.net wrote: Robert, Amit, * Robert Haas (robertmh...@gmail.com) wrote: I don't think that's a typo, although it's not particularly well-worded IMHO. I might rewrite the whole paragraph like this: A policy limits the ability to SELECT, INSERT, UPDATE, or DELETE rows in a table to those rows which match the relevant policy expression. Existing table rows are checked against the expression specified via USING, while new rows that would be created via INSERT or UPDATE are checked against the expression specified via WITH CHECK. Generally, the system will enforce filter conditions imposed using security policies prior to qualifications that appear in the query itself, in order to the prevent the inadvertent exposure of the protected data to user-defined functions which might not be trustworthy. However, functions and operators marked by the system (or the system administrator) as LEAKPROOF may be evaluated before policy expressions, as they are assumed to be trustworthy. Looks reasonable to me. Amit, does this read better for you? If so, I can handle making the change to the docs. I have a wider concern about the wording on this page - both the rewritten paragraph and elsewhere talk about policies in terms of limiting access to or filtering out rows. However, since policy expressions are OR'ed together and there is a default-deny policy when RLS is enabled, I think it should be talking about policies in terms of permitting access to tables that have row security enabled. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] List of table names of a DB
Hello, I am unable to find the function which retrieves the 'list of names of the tables' used in a DB. Reason: I need a mechanism by which I can extract the names of the tables used in a query which has been parsed. My plan is to check for a match of each word in the query with a list of names of the tables used in the current DB so that each hit confirms a 'table name' in the query in most cases. Kindly help.Thanks in advance. Deepak
Re: [HACKERS] Transactions involving multiple postgres foreign servers
Ashutosh Bapat ashutosh.ba...@enterprisedb.com wrote: On Wed, Jan 7, 2015 at 9:50 PM, Kevin Grittner kgri...@ymail.com wrote: Also, as previously mentioned, it must behave in some reasonable way if a database is not configured to support 2PC, especially since 2PC is off by default in PostgreSQL. We can have a per foreign server option, which says whether the corresponding server is able to participate in 2PC. A transaction spanning multiple foreign server with at least one of them not capable of participating in 2PC will be aborted. Will that work? In case a user flags a foreign server as capable to 2PC incorrectly, I expect the corresponding FDW would raise error (either because PREPARE fails or FDW doesn't handle that case) and the transaction will be aborted anyway. That sounds like one way to handle it. I'm not clear on how you plan to determine whether 2PC is required for a transaction. (Apologies if it was previously mentioned and I've forgotten it.) I don't mean to suggest that these problems are insurmountable; I just think that people often underestimate the difficulty of writing a distributed transaction manager and don't always recognize the problems that it will cause if all of the failure modes are not considered and handled. -- Kevin Grittner EDB: 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] Transactions involving multiple postgres foreign servers
On Thu, Jan 8, 2015 at 10:19 AM, Kevin Grittner kgri...@ymail.com wrote: Robert Haas robertmh...@gmail.com wrote: Andres is talking in my other ear suggesting that we ought to reuse the 2PC infrastructure to do all this. If you mean that the primary transaction and all FDWs in the transaction must use 2PC, that is what I was saying, although apparently not clearly enough. All nodes *including the local one* must be prepared and committed with data about the nodes saved safely off somewhere that it can be read in the event of a failure of any of the nodes *including the local one*. Without that, I see this whole approach as a train wreck just waiting to happen. Clearly, all the nodes other than the local one need to use 2PC. I am unconvinced that the local node must write a 2PC state file only to turn around and remove it again almost immediately thereafter. I'm not really clear on the mechanism that is being proposed for doing this, but one way would be to have the PREPARE of the local transaction be requested explicitly and to have that cause all FDWs participating in the transaction to also be prepared. (That might be what Andres meant; I don't know.) We want this to be client-transparent, so that the client just says COMMIT and everything Just Works. That doesn't strike me as the only possible mechanism to drive this, but it might well be the simplest and cleanest. The trickiest bit might be to find a good way to persist the distributed transaction information in a way that survives the failure of the main transaction -- or even the abrupt loss of the machine it's running on. I'd be willing to punt on surviving a loss of the entire machine. But I'd like to be able to survive an abrupt reboot. -- 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] Transactions involving multiple postgres foreign servers
Robert Haas robertmh...@gmail.com wrote: On Thu, Jan 8, 2015 at 10:19 AM, Kevin Grittner kgri...@ymail.com wrote: Robert Haas robertmh...@gmail.com wrote: Andres is talking in my other ear suggesting that we ought to reuse the 2PC infrastructure to do all this. If you mean that the primary transaction and all FDWs in the transaction must use 2PC, that is what I was saying, although apparently not clearly enough. All nodes *including the local one* must be prepared and committed with data about the nodes saved safely off somewhere that it can be read in the event of a failure of any of the nodes *including the local one*. Without that, I see this whole approach as a train wreck just waiting to happen. Clearly, all the nodes other than the local one need to use 2PC. I am unconvinced that the local node must write a 2PC state file only to turn around and remove it again almost immediately thereafter. The key point is that the distributed transaction data must be flagged as needing to commit rather than roll back between the prepare phase and the final commit. If you try to avoid the PREPARE, flagging, COMMIT PREPARED sequence by building the flagging of the distributed transaction metadata into the COMMIT process, you still have the problem of what to do on crash recovery. You really need to use 2PC to keep that clean, I think. I'm not really clear on the mechanism that is being proposed for doing this, but one way would be to have the PREPARE of the local transaction be requested explicitly and to have that cause all FDWs participating in the transaction to also be prepared. (That might be what Andres meant; I don't know.) We want this to be client-transparent, so that the client just says COMMIT and everything Just Works. What about the case where one or more nodes doesn't support 2PC. Do we silently make the choice, without the client really knowing? That doesn't strike me as the only possible mechanism to drive this, but it might well be the simplest and cleanest. The trickiest bit might be to find a good way to persist the distributed transaction information in a way that survives the failure of the main transaction -- or even the abrupt loss of the machine it's running on. I'd be willing to punt on surviving a loss of the entire machine. But I'd like to be able to survive an abrupt reboot. As long as people are aware that there is an urgent need to find and fix all data stores to which clusters on the failed machine were connected via FDW when there is a hard machine failure, I guess it is OK. In essence we just document it and declare it to be somebody else's problem. In general I would expect a distributed transaction manager to behave well in the face of any single-machine failure, but if there is one aspect of a full-featured distributed transaction manager we could give up, I guess that would be it. -- Kevin Grittner EDB: 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] Transactions involving multiple postgres foreign servers
On Wed, Jan 7, 2015 at 11:20 AM, Kevin Grittner kgri...@ymail.com wrote: If we are going to include a distributed transaction manager with PostgreSQL, it *must* persist enough information about the transaction ID and where it is used in a way that will survive a subsequent crash before beginning the PREPARE on any of the systems. After all nodes are PREPAREd it must flag that persisted data to indicate that it is now at a point where ROLLBACK is no longer an option. Only then can it start committing the prepared transactions. After the last node is committed it can clear this information. On start-up the distributed transaction manager must check for any distributed transactions left in progress and commit or rollback based on the preceding; doing retries indefinitely until it succeeds or is told to stop. I think one key question here is whether all of this should be handled in PostgreSQL core or whether some of it should be handled in other ways. Is the goal to make postgres_fdw (and FDWs for other databases that support 2PC) to persist enough information that someone *could* write a transaction manager for PostgreSQL, or is the goal to actually write that transaction manager? Just figuring out how to persist the necessary information is a non-trivial problem by itself. You might think that you could just insert a row into a local table saying, hey, I'm about to prepare a transaction remotely, but of course that doesn't work: if you then go on to PREPARE before writing and flushing the local commit record, then a crash before that's done leaves a dangling prepared transaction on the remote note. You might think to write the record, then after writing and flush the local commit record do the PREPARE. But you can't do that either, because now if the PREPARE fails you've already committed locally. I guess what you need to do is something like: 1. Write and flush a WAL record indicating an intent to prepare, with a list of foreign server OIDs and GUIDs. 2. Prepare the remote transaction on each node. If any of those operations fail, roll back any prepared nodes and error out. 3. Commit locally (i.e. RecordTransactionCommit, writing and flushing WAL). 4. Try to commit the remote transactions. 5. Write a WAL record indicating that you committed the remote transactions OK. If you fail after step 1, you can straighten things out by looking at the status of the transaction: if the transaction committed, any transactions we intended-to-prepare need to be checked. If they are still prepared, we need to commit them or roll them back according to what happened to our XID. (Andres is talking in my other ear suggesting that we ought to reuse the 2PC infrastructure to do all this. I'm not convinced that's a good idea, but I'll let him present his own ideas here if he wants to rather than trying to explain them myself.) -- 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] Transactions involving multiple postgres foreign servers
Robert Haas robertmh...@gmail.com wrote: Andres is talking in my other ear suggesting that we ought to reuse the 2PC infrastructure to do all this. If you mean that the primary transaction and all FDWs in the transaction must use 2PC, that is what I was saying, although apparently not clearly enough. All nodes *including the local one* must be prepared and committed with data about the nodes saved safely off somewhere that it can be read in the event of a failure of any of the nodes *including the local one*. Without that, I see this whole approach as a train wreck just waiting to happen. I'm not really clear on the mechanism that is being proposed for doing this, but one way would be to have the PREPARE of the local transaction be requested explicitly and to have that cause all FDWs participating in the transaction to also be prepared. (That might be what Andres meant; I don't know.) That doesn't strike me as the only possible mechanism to drive this, but it might well be the simplest and cleanest. The trickiest bit might be to find a good way to persist the distributed transaction information in a way that survives the failure of the main transaction -- or even the abrupt loss of the machine it's running on. -- Kevin Grittner EDB: 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
[HACKERS] More Norwegian trouble
There was a long thread on the trouble that the Windows Norwegian (Bokmål) locale name causes, because the locale name is not pure ASCII. That was fixed by mapping Norwegian (Bokmål) to a pure-ASCII alias of it, norwegian-bokmal. (http://www.postgresql.org/message-id/20140915230427.2486.29...@wrigleys.postgresql.org) I just upgraded my Windows toolchain, and as a leftover from developing that patch still had my locale set to Norwegian (Bokmål). To my surprise, after a rebuild, initdb failed: FATAL: new collation (Norwegian_Norway.1252) is incompatible with the collation of the template database (norwegian-bokmal_Norway.1252) HINT: Use the same collation as in the template database, or use template0 as t emplate. STATEMENT: CREATE DATABASE template0 IS_TEMPLATE = true ALLOW_CONNECTIONS = fal se; It works when I pass a locale to initdb explicitly; it only breaks if the system locale is set to Norwegian (Bokmål), and I let initdb to use the default. At first I suspected Noah's commit 6fdba8ceb071a3512d5685f1cd4f971ab4d562d1, but reverting that made no difference. So unfortunately, that patch that I committed earlier did not completely fix this issue. It looks like setlocale() is quite brain-dead on what the canonical spelling of that locale is: setlocale(LC_COLLATE, NULL) - Norwegian (Bokmål)_Norway but: setlocale(LC_COLLATE, norwegian-bokmal_Norway) - Norwegian_Norway) Apparently the behavior changed when I upgraded the toolchain. IIRC, I used to use Microsoft Windows SDK 7.1, with Microsoft Visual C++ Compilers 2010 Standard Edition that came with it. I'm now using Microsoft Visual Studio Community Edition 2013 Update 4, with Microsoft Visual C++ Compilers 2010 SP Standard. I don't know what part of the upgrade broke this. Could also have been something else; I don't keep track of my build environment that carefully. Now, what should we do about this? I'd like to know if others are seeing this, with whatever compiler versions you are using. In particular, I wonder if the builds included in the EnterpriseDB installers are experiencing this. Perhaps the nicest fix would be to change the mapping code to map the problematic locale name to Norwegian_Norway instead of norwegian-bokmal. That's assuming that it is in fact the same locale, and that it's accepted on all supported Windows versions. Another option is to also map Norwegian_Norway to norwegian-bokmal_Norway, even though Norwegian_Norway doesn't contain any ASCII characters and wouldn't be a problem as such. That seems like a safer option. It would be good to do something about this before the next minor release, as the original mapping commit has not been released yet. - Heikki -- 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] Compression of full-page-writes
Hello, Below are performance numbers in case of synchronous replication with and without fpw compression using latest version of patch(version 14). The patch helps improve performance considerably. Both master and standby are on the same machine in order to get numbers independent of network overhead. The compression patch helps to increase tps by 10% . It also helps reduce I/O to disk , latency and total runtime for a fixed number of transactions as shown below. The compression of WAL is quite high around 40%. pgbench scale :1000 pgbench command : pgbench -c 16 -j 16 -r -t 25 -M prepared To ensure that data is not highly compressible, empty filler columns were altered using alter table pgbench_accounts alter column filler type text using gen_random_uuid()::text checkpoint_segments = 1024 checkpoint_timeout = 5min fsync = on Compressionon off WAL generated 23037180520(~23.04MB) 38196743704(~38.20MB) TPS 264.18 239.34 Latency average60.541 ms 66.822 ms Latency stddev 126.567 ms 130.434 ms Total writes to disk 145045.310 MB192357.250 MB Runtime 15141.0 s 16712.0 s Server specifications: Processors:Intel® Xeon ® Processor E5-2650 (2 GHz, 8C/16T, 20 MB) * 2 nos RAM: 32GB Disk : HDD 450GB 10K Hot Plug 2.5-inch SAS HDD * 8 nos 1 x 450 GB SAS HDD, 2.5-inch, 6Gb/s, 10,000 rpm Thank you, Rahila Syed -- View this message in context: http://postgresql.nabble.com/Compression-of-full-page-writes-tp5769039p5833315.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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_rewind in contrib
On 01/06/2015 09:13 AM, Michael Paquier wrote:
Some more comments:
- Nitpicking: the header formats of filemap.c, datapagemap.c,
datapagemap.h and util.h are incorrect (I pushed a fix about that in
pg_rewind itself, feel free to pick it up).
Ah, fixed.
- parsexlog.c has a copyright mention to Nippon Telegraph and
Telephone Corporation. Cannot we drop it safely?
Removed. The file used to contain code for handling different WAL record
types that was originally copied from pg_lesslog, hence the NTT
copyright. However, that code is no longer there.
- Error codes needs to be generated before building pg_rewind. If I do
for example a simple configure followed by make I get a failure:
$ ./configure
$ cd contrib/pg_rewind make
In file included from parsexlog.c:16:
In file included from ../../src/include/postgres.h:48:
../../src/include/utils/elog.h:69:10: fatal error: 'utils/errcodes.h'
file not found
#include utils/errcodes.h
Many other contrib modules have the same problem. And other
subdirectories too. It's not something that this patch needs to fix.
- MSVC build is not supported yet. You need to do something similar to
pg_xlogdump, aka some magic with for example xlogreader.c.
- Build fails with MinGW as there is visibly some unportable code:
Fixed all the errors I got on MSVC. The biggest change was rewriting the
code that determines if a file is a relation file, based on its
filename. It used a regular expression, which I replaced with a bunch of
sscanf calls, and a cross-check that GetRelationPath() returns the same
filename.
copy_fetch.c: In function 'check_samefile':
copy_fetch.c:298:2: warning: passing argument 2 of '_fstat64i32' from incompatib
le pointer type [enabled by default]
if (fstat(fd1, statbuf1) 0)
^
In file included from ../../src/include/port.h:283:0,
from ../../src/include/c.h:1050,
from ../../src/include/postgres_fe.h:25,
from copy_fetch.c:10:
c:\mingw\include\sys\stat.h:200:32: note: expected 'struct _stat64i32 *' but arg
ument is of type 'struct stat *'
__CRT_MAYBE_INLINE int __cdecl _fstat64i32(int desc, struct _stat64i32 *_stat)
{
Strange. There isn't anything special about the fstat() calls in
pg_rewind. Do you get these from other modules that call fstat, e.g.
pg_stat_statements?
I did not see these warnings when building with MSVC, and don't have
MinGW installed currently.
Hm. I think that this is something we should try to fix first
upstream.
Yeah, possibly. But here's a new patch anyway.
- Heikki
pg_rewind-contrib-4.patch.gz
Description: application/gzip
--
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] Possible typo in create_policy.sgml
Dean, * Dean Rasheed (dean.a.rash...@gmail.com) wrote: [There's also a typo further down -- filter out the records which are visible, should be not visible] What do you think of the attached rewording? Rewording it this way is a great idea. Hopefully that will help address the confusion which we've seen. The only comment I have offhand is: should we should add a sentence to this paragraph about the default-deny policy? I feel like that would help explain why the policies are allowing access to rows. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] [RFC] LSN Map
On 1/7/15, 3:50 AM, Marco Nenciarini wrote: The current implementation tracks only heap LSN. It currently does not track any kind of indexes, but this can be easily added later. Would it make sense to do this at a buffer level, instead of at the heap level? That means it would handle both heap and indexes. I don't know if LSN is visible that far down though. Also, this pattern is repeated several times; it would be good to put it in it's own function: + lsnmap_pin(reln, blkno, lmbuffer); + lsnmap_set(reln, blkno, lmbuffer, lsn); + ReleaseBuffer(lmbuffer); -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] VODKA?
On 1/7/15, 3:26 PM, Arthur Silva wrote: On Jan 6, 2015 7:14 PM, Josh Berkus j...@agliodbs.com mailto:j...@agliodbs.com wrote: Oleg, Teodor: I take it VODKA is sliding to version 9.6? This is kinda off, but I was wondering if anyone ever considered running a crowd-funding campaign for this sort of feature (aka potentially very popular). I don't know if Teodor or Oleg are in a position to accept funding, but it is an interesting idea. Perhaps it would be useful to try this with a different feature. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] Parallel Seq Scan
On 1/5/15, 9:21 AM, Stephen Frost wrote: * Robert Haas (robertmh...@gmail.com) wrote: I think it's right to view this in the same way we view work_mem. We plan on the assumption that an amount of memory equal to work_mem will be available at execution time, without actually reserving it. Agreed- this seems like a good approach for how to address this. We should still be able to end up with plans which use less than the max possible parallel workers though, as I pointed out somewhere up-thread. This is also similar to work_mem- we certainly have plans which don't expect to use all of work_mem and others that expect to use all of it (per node, of course). I agree, but we should try and warn the user if they set parallel_seqscan_degree close to max_worker_processes, or at least give some indication of what's going on. This is something you could end up beating your head on wondering why it's not working. Perhaps we could have EXPLAIN throw a warning if a plan is likely to get less than parallel_seqscan_degree number of workers. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] Parallel Seq Scan
* Jim Nasby (jim.na...@bluetreble.com) wrote: On 1/5/15, 9:21 AM, Stephen Frost wrote: * Robert Haas (robertmh...@gmail.com) wrote: I think it's right to view this in the same way we view work_mem. We plan on the assumption that an amount of memory equal to work_mem will be available at execution time, without actually reserving it. Agreed- this seems like a good approach for how to address this. We should still be able to end up with plans which use less than the max possible parallel workers though, as I pointed out somewhere up-thread. This is also similar to work_mem- we certainly have plans which don't expect to use all of work_mem and others that expect to use all of it (per node, of course). I agree, but we should try and warn the user if they set parallel_seqscan_degree close to max_worker_processes, or at least give some indication of what's going on. This is something you could end up beating your head on wondering why it's not working. Perhaps we could have EXPLAIN throw a warning if a plan is likely to get less than parallel_seqscan_degree number of workers. Yeah, if we come up with a plan for X workers and end up not being able to spawn that many then I could see that being worth a warning or notice or something. Not sure what EXPLAIN has to do anything with it.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Turning recovery.conf into GUCs
On 1/6/15 4:40 PM, Josh Berkus wrote: Btw., I'm not sure that everyone will be happy to have primary_conninfo visible, since it might contain passwords. Didn't we discuss this? I forgot what the conclusion was ... probably not to put passwords in primary_conninfo. One can always say, don't do that then. But especially with pg_basebackup -R mindlessly copying passwords from .pgpass into recovery.conf, the combination of these factors would proliferate passwords a bit too easily for my taste. Maybe a separate primary_conninfo_password that is a kind of write-only GUC would work. (That's how passwords usually work: You can change your password, but can't see your existing one.) -- 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] Turning recovery.conf into GUCs
On 1/6/15 8:08 PM, Andres Freund wrote: On 2015-01-05 20:43:12 -0500, Peter Eisentraut wrote: For example, putting recovery target parameters into postgresql.conf might not make sense to some people. Once you have reached the recovery end point, the information is obsolete. Keeping it set could be considered confusing. I don't know, but I think that ship has sailed. hot_standby, archive_command, archive_mode, hot_standby_feedback are all essentially treated differently between primary and standby. I don't mind those. I mean things like recovery_target_time. Moreover, when I'm actually doing point-in-time-recovery attempts, I don't think I want to be messing with my pristine postgresql.conf file. Having those settings separate isn't a bad idea in that case. Well, nothing stops you from having a include file or something similar. Sure, I need to update postgresql.conf to have an include file. I think we should just make recovery.conf behave exactly the way it does right now, except parse it according to guc rules. That way the changes when migrating are minimal and we don't desupport any usecases. Disallowing that way of operating just seems like intentionally throwing rocks in the way of getting this done. That was more or less my proposal. The current system makes it easy to share postgresql.conf between primary and standby and just maintain the information related to the standby locally in recovery.conf. pg_basebackup -R makes that even easier. It's still possible to do this in the new system, but it's decidedly more work. Really? Howso? You have to set up include files, override the include file on the standby, I don't know how pg_basebackup -R would even work. And most importantly, you have to come up with all of that yourself, instead of it just working. The wins on the other hand are obscure: You can now use SHOW to inspect recovery settings. You can design your own configuration file include structures to set them. These are not bad, but is that all? It's much more: a) One configuration format instead of two somewhat, but not really, similar ones. Agreed, but that's also fixable by just changing how recovery.conf is parsed. It doesn't require user space changes. b) Proper infrastructure to deal with configuration variable boundaries and such. Just a few days ago there was e7c11887 et al. That's just an implementation issue. c) Infrastructure for changing settings effective during recovery. Right now we'd have to rebuild a lot of guc infrasturcture to allow that. It'd not be that hard to allow changing parameters like restore_command, primary_conninfo, recovery_target_* et al. That's for sure not the same commit, but once the infrastructure is in those won't be too hard. Right, if that worked, then it would be a real win. But this discussion is about right now, and the perspective of the user. -- 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_rewind in contrib
On 1/6/15 7:17 PM, Andres Freund wrote: One problem is that it doesn't use the replication protocol, so the setup is going to be inconsistent with pg_basebackup. Maybe the replication protocol could be extended to provide the required data. I'm not particularly bothered by the requirement of also requiring a normal, not replication, connection. In most cases that'll already be allowed. I don't agree. We have separated out replication access, especially in pg_hba.conf and with the replication role attribute, for a reason. (I hope there was a reason; I don't remember.) It is not unreasonable to set things up so that non-replication access is only from the application tier, and replication access is only from within the database tier. Now we're saying, well, we didn't really mean that, in order to use the latest replication management tools, you also need to open up non-replication access, but we assume you already do that anyway. Now I understand that making pg_rewind work over a replication connection is a lot more work, and maybe we don't want to spend it, at least right now. But then we either need to document this as an explicit deficiency and think about fixing it later, or we should revisit how replication access control is handled. -- 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] Patch: [BUGS] BUG #12320: json parsing with embedded double quotes
On 01/08/2015 03:05 PM, Aaron Botsis wrote: It's also unnecessary. CSV format, while not designed for this, is nevertheless sufficiently flexible to allow successful import of json data meeting certain criteria (essentially no newlines), like this: copy the_table(jsonfield) from '/path/to/jsondata' csv quote e'\x01' delimiter e'\x02’; While perhaps unnecessary, given the size and simplicity of the patch, IMO it’s a no brainer to merge (it actually makes the code smaller by 3 lines). It also enables non-json use cases anytime one might want to preserve embedded escapes, or use different ones entirely. Do you see other reasons not to commit it? Well, for one thing it's seriously incomplete. You need to be able to change the delimiter as well. Otherwise, any embedded tab in the json will cause you major grief. Currently the delimiter and the escape MUST be a single byte non-nul character, and there is a check for this in csv mode. Your patch would allow any arbitrary string (including one of zero length) for the escape in text mode, and would then silently ignore all but the first byte. That's not the way we like to do things. And, frankly, I would need to spend quite a lot more time thinking about other implications than I have given it so far. This is an area where I tend to be VERY cautious about making changes. This is a fairly fragile ecosystem. 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] make check-world regress failed
On 2014-12-04 16:38:45 +0200, Heikki Linnakangas wrote:
On 11/23/2014 08:37 PM, Vladimir Koković wrote:
PostgreSQL check-world regress failed with current GIT HEAD on my Kubuntu
14.10.
uname -a
Linux vlD-kuci 3.16.0-24-generic #32-Ubuntu SMP Tue Oct 28 13:13:18 UTC
2014 i686 athlon i686 GNU/Linux
gdb -d /home/src/postgresql-devel/postgresql-git/postgresql/src -c core
...
Loaded symbols for
/home/src/postgresql-devel/dev-build/src/test/regress/regress.so
(gdb) bt
#0 0xb76ecc7c in __kernel_vsyscall ()
#1 0xb7075577 in __GI_raise (sig=sig@entry=6) at
../nptl/sysdeps/unix/sysv/linux/raise.c:56
#2 0xb7076cf3 in __GI_abort () at abort.c:89
#3 0x084c326a in ?? ()
#4 0x0a56c3b8 in ?? ()
#5 0xb76d232f in pg_atomic_init_u64 (ptr=0xbfa16fd4, val=0) at
/home/src/postgresql-devel/postgresql-git/postgresql/src/include/port/atomics.h:445
#6 0xb76d50e4 in test_atomic_uint64 () at
/home/src/postgresql-devel/postgresql-git/postgresql/src/test/regress/regress.c:1022
#7 0xb76d5756 in test_atomic_ops (fcinfo=0xa57c76c) at
/home/src/postgresql-devel/postgresql-git/postgresql/src/test/regress/regress.c:1114
#8 0x0825bfee in ?? ()
...
Andres, have you had a chance to look at this?
Nope, missed it somehow.
On 32-bit x86, arch-x86.h leaves PG_HAVE_ATOMIC_U64_SUPPORT undefined. But
generic-gcc.h, which is included later, then defines it.
That's fine. The only reason arch-x86.h implements anything itself is
that that allows older compilers than relying on intrinsics. But
implementing 64bit atomics is too annoying by hand and isn't currently
required.
pg_atomic_init_u64 does AssertPointerAlignment(ptr, 8) on the variable, but
there is no guarantee that it is 8-bytes aligned on x86.
Hrmpf. Annoying. Gcc for a while claimed that was guaranteed, but, if I
understood the tickets correctly, gave up on that.
Unfortunately we have to rely (IIRC) on that for (quite old) x86s and
some other architectures. It doesn't seem to be a problem on any native
64bit platform, because 64bit variables are 8byte aligned natively
there.
I think it can relatively easily be fixed by something like the
attached. Don't have a pure 32bit environment to test though - the
problem isn't reproducable in a 32bit chroot...
Vladimir, if you apply that patch, do things work for you?
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
diff --git a/src/include/port/atomics/arch-x86.h b/src/include/port/atomics/arch-x86.h
index fb5623d..168a49c 100644
--- a/src/include/port/atomics/arch-x86.h
+++ b/src/include/port/atomics/arch-x86.h
@@ -73,6 +73,7 @@ typedef struct pg_atomic_uint32
#define PG_HAVE_ATOMIC_U64_SUPPORT
typedef struct pg_atomic_uint64
{
+ /* alignment guaranteed due to being on a 64bit platform */
volatile uint64 value;
} pg_atomic_uint64;
#endif
diff --git a/src/include/port/atomics/generic-acc.h b/src/include/port/atomics/generic-acc.h
index e16e282..c5639aa 100644
--- a/src/include/port/atomics/generic-acc.h
+++ b/src/include/port/atomics/generic-acc.h
@@ -40,6 +40,12 @@ typedef struct pg_atomic_uint32
#define PG_HAVE_ATOMIC_U64_SUPPORT
typedef struct pg_atomic_uint64
{
+ /*
+ * Alignment is guaranteed to be 64bit. Search for Well-behaved
+ * application restrictions = Data alignment and data sharing on HP's
+ * website. Unfortunately the URL doesn't seem to stable enough to
+ * include.
+ */
volatile uint64 value;
} pg_atomic_uint64;
diff --git a/src/include/port/atomics/generic-gcc.h b/src/include/port/atomics/generic-gcc.h
index f19ad34..fea1cb5 100644
--- a/src/include/port/atomics/generic-gcc.h
+++ b/src/include/port/atomics/generic-gcc.h
@@ -98,7 +98,7 @@ typedef struct pg_atomic_uint32
typedef struct pg_atomic_uint64
{
- volatile uint64 value;
+ volatile uint64 value __attribute__((aligned(8)));
} pg_atomic_uint64;
#endif /* defined(HAVE_GCC__ATOMIC_INT64_CAS) || defined(HAVE_GCC__SYNC_INT64_CAS) */
diff --git a/src/include/port/atomics/generic-msvc.h b/src/include/port/atomics/generic-msvc.h
index 1d763ab..d259d6f 100644
--- a/src/include/port/atomics/generic-msvc.h
+++ b/src/include/port/atomics/generic-msvc.h
@@ -41,7 +41,7 @@ typedef struct pg_atomic_uint32
} pg_atomic_uint32;
#define PG_HAVE_ATOMIC_U64_SUPPORT
-typedef struct pg_atomic_uint64
+typedef struct __declspec(align(8)) pg_atomic_uint64
{
volatile uint64 value;
} pg_atomic_uint64;
diff --git a/src/include/port/atomics/generic-sunpro.h b/src/include/port/atomics/generic-sunpro.h
index b756fb9..7a3028e 100644
--- a/src/include/port/atomics/generic-sunpro.h
+++ b/src/include/port/atomics/generic-sunpro.h
@@ -55,7 +55,13 @@ typedef struct pg_atomic_uint32
#define PG_HAVE_ATOMIC_U64_SUPPORT
typedef struct pg_atomic_uint64
{
- volatile uint64 value;
+ /*
+ * Syntax to enforce variable alignment should be supported by versions
+ * supporting atomic.h, but it's hard to find accurate documentation. If
