[HACKERS] digest
set pgsql-hackers digest -- 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] elegant and effective way for running jobs inside a database
2012/3/5 Artur Litwinowicz ad...@ybka.com Dear Developers, I am looking for elegant and effective way for running jobs inside a database or cluster - for now I can not find that solution. OK if You say use cron or pgAgent I say I know that solutions, but the are not effective and elegant. Compilation of pgAgent is a pain (especially wxWidgets usage on system with no X) - it can run jobs with minimal 60s periods but what when someone needs run it faster for eg. with 5s period ? Of course using cron I can do that but it is not effective and elegant solution. Why PostgreSQL can not have so elegant solution like Oracle database ? I am working with Oracle databases for many years, but I like much more PostgreSQL project but this one thing... I can not understand - the lack of jobs inside the database... IMHO it is not necessary add this feature to the PostgreSQL core, because the OS already has the capability to schedule and maintain the tasks. Best regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] VALID UNTIL
2012/3/14 David Fetter da...@fetter.org I don't know how frequently people use VALID UNTIL, but I'm guessing it's not terribly often because yours is the first comment about how it's not exposed, so I'd tend toward putting it in attributes rather than a separate column. If it's desired I can write a patch to put Valid until into attributes column. -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] VALID UNTIL
2012/3/14 David Fetter da...@fetter.org
I don't know how frequently people use VALID UNTIL, but I'm guessing
it's not terribly often because yours is the first comment about how
it's not exposed, so I'd tend toward putting it in attributes rather
than a separate column.
The attached patch put VALID UNTIL into attributes column in verbose mode
like example above.
bdteste=# \du
List of roles
Role name | Attributes | Member of
---++---
bucardo | Superuser, Create role, Create DB, Replication | {}
postgres | Superuser, Create role, Create DB, Replication | {}
bdteste=# \du+
List of roles
Role name |Attributes
| Member of | Description
---+---+---+--
bucardo | Superuser, Create role, Create DB, Valid until 2012-12-31
23:59:59.99-02, Replication | {}| bucardo role
postgres | Superuser, Create role, Create DB, Replication
| {}|
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
***
*** 2382,2388 describeRoles(const char *pattern, bool verbose)
ARRAY(SELECT b.rolname\n
FROM pg_catalog.pg_auth_members m\n
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n
! WHERE m.member = r.oid) as memberof);
if (verbose pset.sversion = 80200)
{
--- 2382,2389
ARRAY(SELECT b.rolname\n
FROM pg_catalog.pg_auth_members m\n
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n
! WHERE m.member = r.oid) as memberof,\n
! 'Valid until '::text||r.rolvaliduntil::text as rolvaliduntil);
if (verbose pset.sversion = 80200)
{
***
*** 2407,2413 describeRoles(const char *pattern, bool verbose)
true AS rolinherit, false AS rolcreaterole,\n
u.usecreatedb AS rolcreatedb, true AS rolcanlogin,\n
-1 AS rolconnlimit,\n
! ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof
\nFROM pg_catalog.pg_user u\n);
processSQLNamePattern(pset.db, buf, pattern, false, false,
--- 2408,2415
true AS rolinherit, false AS rolcreaterole,\n
u.usecreatedb AS rolcreatedb, true AS rolcanlogin,\n
-1 AS rolconnlimit,\n
! ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof,\n
! 'Valid until '::text||u.valuntil::text as rolvaliduntil
\nFROM pg_catalog.pg_user u\n);
processSQLNamePattern(pset.db, buf, pattern, false, false,
***
*** 2452,2459 describeRoles(const char *pattern, bool verbose)
if (strcmp(PQgetvalue(res, i, 5), t) != 0)
add_role_attribute(buf, _(Cannot login));
if (pset.sversion = 90100)
! if (strcmp(PQgetvalue(res, i, (verbose ? 9 : 8)), t) == 0)
add_role_attribute(buf, _(Replication));
conns = atoi(PQgetvalue(res, i, 6));
--- 2454,2464
if (strcmp(PQgetvalue(res, i, 5), t) != 0)
add_role_attribute(buf, _(Cannot login));
+ if (strcmp(PQgetvalue(res, i, 8), ) != 0 verbose)
+ add_role_attribute(buf, PQgetvalue(res, i, 8));
+
if (pset.sversion = 90100)
! if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), t) == 0)
add_role_attribute(buf, _(Replication));
conns = atoi(PQgetvalue(res, i, 6));
***
*** 2478,2485 describeRoles(const char *pattern, bool verbose)
printTableAddCell(cont, PQgetvalue(res, i, 7), false, false);
if (verbose pset.sversion = 80200)
! printTableAddCell(cont, PQgetvalue(res, i, 8), false, false);
! }
termPQExpBuffer(buf);
printTable(cont, pset.queryFout, pset.logfile);
--- 2483,2491
printTableAddCell(cont, PQgetvalue(res, i, 7), false, false);
if (verbose pset.sversion = 80200)
! printTableAddCell(cont, PQgetvalue(res, i, 9), false, false);
!
! }
termPQExpBuffer(buf);
printTable(cont, pset.queryFout, pset.logfile);
--
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] VALID UNTIL
2012/3/14 Tom Lane t...@sss.pgh.pa.us
Why would you confine it to verbose mode?
Because I did not want to change the current behavior of this psql
command... but...
For most people it won't
matter, but for people who are using the feature, it seems like
important information. Per the OP's complaint, it's particularly
important for those who have forgotten they're using the feature
(and hence would not think to specify + ...)
You' re right, then I attached a new patch with your suggestion.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
***
*** 2382,2388 describeRoles(const char *pattern, bool verbose)
ARRAY(SELECT b.rolname\n
FROM pg_catalog.pg_auth_members m\n
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n
! WHERE m.member = r.oid) as memberof);
if (verbose pset.sversion = 80200)
{
--- 2382,2389
ARRAY(SELECT b.rolname\n
FROM pg_catalog.pg_auth_members m\n
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n
! WHERE m.member = r.oid) as memberof,\n
! 'Valid until '::text||r.rolvaliduntil::text as rolvaliduntil);
if (verbose pset.sversion = 80200)
{
***
*** 2407,2413 describeRoles(const char *pattern, bool verbose)
true AS rolinherit, false AS rolcreaterole,\n
u.usecreatedb AS rolcreatedb, true AS rolcanlogin,\n
-1 AS rolconnlimit,\n
! ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof
\nFROM pg_catalog.pg_user u\n);
processSQLNamePattern(pset.db, buf, pattern, false, false,
--- 2408,2415
true AS rolinherit, false AS rolcreaterole,\n
u.usecreatedb AS rolcreatedb, true AS rolcanlogin,\n
-1 AS rolconnlimit,\n
! ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof,\n
! 'Valid until '::text||u.valuntil::text as rolvaliduntil
\nFROM pg_catalog.pg_user u\n);
processSQLNamePattern(pset.db, buf, pattern, false, false,
***
*** 2452,2459 describeRoles(const char *pattern, bool verbose)
if (strcmp(PQgetvalue(res, i, 5), t) != 0)
add_role_attribute(buf, _(Cannot login));
if (pset.sversion = 90100)
! if (strcmp(PQgetvalue(res, i, (verbose ? 9 : 8)), t) == 0)
add_role_attribute(buf, _(Replication));
conns = atoi(PQgetvalue(res, i, 6));
--- 2454,2464
if (strcmp(PQgetvalue(res, i, 5), t) != 0)
add_role_attribute(buf, _(Cannot login));
+ if (strcmp(PQgetvalue(res, i, 8), ) != 0 verbose)
+ add_role_attribute(buf, PQgetvalue(res, i, 8));
+
if (pset.sversion = 90100)
! if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), t) == 0)
add_role_attribute(buf, _(Replication));
conns = atoi(PQgetvalue(res, i, 6));
***
*** 2478,2485 describeRoles(const char *pattern, bool verbose)
printTableAddCell(cont, PQgetvalue(res, i, 7), false, false);
if (verbose pset.sversion = 80200)
! printTableAddCell(cont, PQgetvalue(res, i, 8), false, false);
! }
termPQExpBuffer(buf);
printTable(cont, pset.queryFout, pset.logfile);
--- 2483,2491
printTableAddCell(cont, PQgetvalue(res, i, 7), false, false);
if (verbose pset.sversion = 80200)
! printTableAddCell(cont, PQgetvalue(res, i, 9), false, false);
!
! }
termPQExpBuffer(buf);
printTable(cont, pset.queryFout, pset.logfile);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Proposal: Store timestamptz of database creation on pg_database
Hi all,
This proposal is about add a column datcreated on pg_database to store
the timestamp of the database creation.
A couple weeks ago I had a trouble with a PostgreSQL instance, actually our
ERP had some strange behaviors with some data loss, but I searched for
ERRORs in log files (OS and PG) and I found nothing.
Looking at the files and directories in the cluster noticed something
strange, the date / time of the file base//PG_VERSION (database of
our ERP) was different compared to when we create it. So I used the
following SQL to check the date / time of creation of the databases in the
cluster:
fabrizio=# SELECT datname,
(pg_stat_file('base/'||oid||'/PG_VERSION')).modification AS datcreated
fabrizio-# FROM pg_database;
datname | datcreated
---+
template1 | 2012-12-26 12:11:53-02
template0 | 2012-12-26 12:11:54-02
postgres | 2012-12-26 12:11:54-02
fabrizio | 2012-12-26 12:12:02-02
(4 rows)
This isn't an elegant solution to do that, but worked fine. However, why
not we have a column to store this information?
Somebody have another idea?
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On Thu, Dec 27, 2012 at 2:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: This has been debated, and rejected, before. I know this discussion... To mention just one problem, are we going to add nonstandard, non-backwards-compatible syntax to every single kind of CREATE to allow pg_dump to preserve the creation dates? Another interesting question is whether we should likewise track the last ALTER time, or perhaps whether a sufficiently major ALTER redefinition should update the creation time. I agree with you because now we have Event Triggers... I'm inclined to think that anyone who really needs this should be pointed at event triggers. That feature (if it gets in) will allow people to track creation/DDL-change times with exactly the behavior they want. Exactly, but Event Triggers [1] don't cover CREATE DATABASE statement, and for this reason I propose the patch to add a single column datcreated on shared catalog pg_database. [1] http://www.postgresql.org/docs/devel/static/event-trigger-matrix.html Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On Thu, Dec 27, 2012 at 2:04 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Tom Lane t...@sss.pgh.pa.us writes: This proposal is about add a column datcreated on pg_database to store the timestamp of the database creation. I'm inclined to think that anyone who really needs this should be pointed at event triggers. That feature (if it gets in) will allow people to track creation/DDL-change times with exactly the behavior they want. Agreed. +1 Maybe the best way to reconciliate both your views would be to provide the previous example in the event trigger docs? +1 If all of you agree I can improve the event trigger docs... Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
Hi all, And about proposal that originated this thread... I proposed only to add a column on shared catalog pg_database with timestamp of its creation. Event triggers don't cover CREATE DATABASE statement. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
[HACKERS] Minor fix in 'clean' action of 'src/backend/Makefile'
Hi all, When we execute 'make clean' the file 'src/backend/replication/repl_gram.h' is not removed. The attached patch fix it. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello fix_clean_src_backend_makefile.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] Minor fix in 'clean' action of 'src/backend/Makefile'
On Wed, Jan 2, 2013 at 2:23 PM, Heikki Linnakangas hlinnakan...@vmware.comwrote: On 02.01.2013 18:20, Heikki Linnakangas wrote: Hmm, looking closer though, repl_gram.h is not actually needed for anything, though. We could just remove the -d flag from the bison invocation and not build it to begin with. I'll go and do that.. And looking even closer, we don't use the -d flag in git master anymore, so repl_gram.h is not being built, and there's nothing to do here. I suppose we could change back-branches to also not build it, but I don't think I'm going to bother. You all right... thanks -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
* Robert Haas robertmh...@gmail.com wrote: I know this has been discussed and rejected before, but I find that rejection to be wrong-headed. I have repeatedly been asked, with levels of exasperation ranging from mild to homicidal, why we don't have this feature, and I have no good answer. If it were somehow difficult to record this or likely to produce a lot of overhead, that would be one thing. But it isn't. It's probably a hundred-line patch, and AFAICS the overhead would be miniscule. Hi all, The attached patch add a new column into 'pg_database' called 'datcreated' to store the timestamp of database creation. If this feature is approved I could extend it to add a column into 'pg_class' to store creation timestamp too. I think we can discuss about psql support to show this new info about databases... Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello pg_database_add_datcreated_column_v1.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] Proposal: Store timestamptz of database creation on pg_database
On Thu, Jan 3, 2013 at 11:13 AM, Stephen Frost sfr...@snowman.net wrote: * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: The attached patch add a new column into 'pg_database' called 'datcreated' to store the timestamp of database creation. Please use hard-tabs (not spaces) and the column should come before the variable length records (see the comment in pg_database.h). You all right... I fixed it in attached patch. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello pg_database_add_datcreated_column_v2.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] Proposal: Store timestamptz of database creation on pg_database
On Thu, Jan 3, 2013 at 11:33 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: On Thu, Jan 3, 2013 at 11:13 AM, Stephen Frost sfr...@snowman.net wrote: * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: The attached patch add a new column into 'pg_database' called 'datcreated' to store the timestamp of database creation. Please use hard-tabs (not spaces) and the column should come before the variable length records (see the comment in pg_database.h). You all right... I fixed it in attached patch. Please... discard this patch... I make a mistake... soon I send the new one. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Proposal: Store timestamptz of database creation on pg_database
On Thu, Jan 3, 2013 at 11:41 AM, Stephen Frost sfr...@snowman.net wrote: * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: On Thu, Jan 3, 2013 at 11:13 AM, Stephen Frost sfr...@snowman.net wrote: Please use hard-tabs (not spaces) and the column should come before the variable length records (see the comment in pg_database.h). You all right... I fixed it in attached patch. You also need to fix the Anum_* values to match what's in the struct definition now.. I'd recommend that you look over the code more closely and ensure that you're ordering everything correctly throughout and that it all makes sense.. Now I fixed it. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello pg_database_add_datcreated_column_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] Proposal: Store timestamptz of database creation on pg_database
On Thu, Jan 3, 2013 at 12:30 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Peter Eisentraut escribió: If we're going to store object creation time, I think we should do it for all objects, stored in a separate catalog, like pg_depend or pg_description, keyed off classid, objectid. And have a simple C function to call to update the information stored there. +1 +1 We require two catalogs though, one shared, one database-local. Have you a suggestion for the names of this new two catalogs? Would we track ctime of subsidiary objects such as constraints etc? If we're going to this way I think yes... As Peter said we can start add it for a few commands in one release (maybe first for shared objects) and then for a few more commands in a next release, and next... until we cover all commands... Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Re: Proposal: Store timestamptz of database creation on pg_database
On Fri, Jan 4, 2013 at 4:07 PM, Peter Eisentraut pete...@gmx.net wrote: On 1/3/13 3:26 PM, Robert Haas wrote: It's true, as we've often said here, that leveraging the OS facilities means that we get the benefit of improving OS facilities for free - but it also means that we never exceed what the OS facilities are able to provide. And that should be the deciding factor, shouldn't it? Clearly, the OS timestamps do not satisfy the requirements of tracking database object creation times. +1 And IMHO we must decide what we do or if we'll don't anything. In this thread was discussed many ways to how to implement and how not implement, so I compile some important points discussed before (sorry if I forgot something): * the original proposal was just to add a column in shared catalog 'pg_database' to track creation time (I already sent a patch [1]), but the discussion going to implement a way to track creation time off all database objects * some people said if we implement that then we must have some way to alter creation times by SQL (ALTER cmds) and also have a way to dump and restore this info by pg_dump/pg_restore. Some agreed and others disagree. * we talk about implement it with EventTriggers, but they not cover shared objects (like databases, roles, tablespaces,...), and someone talked to extend EventTriggers to cover this kind of objects or maybe we have a way to create *shared tables* (this is what I understood). This way force to every people implement your own track time system or maybe someone share a extension to do that. * also we discuss about create two new catalogs, one local and another shared (like pg_description and pg_shdescription) to track creation times of all database objects. Please fix if I forgot something. Anyway, we must decide what to do. I don't know enough, but I have another idea. What you guys think about we have tables like stats tables to track creation times, with a GUC to enable or disable this behavior. Regards, [1] http://archives.postgresql.org/pgsql-hackers/2013-01/msg00111.php -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Re: Proposal: Store timestamptz of database creation on pg_database
* Stephen Frost sfr...@snowman.net wrote: * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: * also we discuss about create two new catalogs, one local and another shared (like pg_description and pg_shdescription) to track creation times of all database objects. Creating a separate catalog (or two) every time we want to track XYZ for all objects is rather overkill... Thinking about this a bit more, and noting that pg_description/shdescription more-or-less already exist as a framework for tracking 'something' for 'all catalog entries'- why don't we just add these columns to those tables..? But those tables are filled only when we execute COMMENT ON statement... then your idea is create a 'null' comment every time we create a single object... is it? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Re: Proposal: Store timestamptz of database creation on pg_database
* Stephen Frost sfr...@snowman.net wrote: Yes, and have the actual 'description' field (as it's variable) at the end of the catalog. Regarding the semantics of it- I was thinking about how directories and unix files work. Basically, adding or removing a sub-object would update the alter time on the object itself, changing an already existing object or sub-object would update only the object/sub-object's alter time. Creating an object or sub/object would set its create time and alter time to the same value. I would distinguish 'create' from 'ctime', however, and have our 'create' time be only the actual *creation* time of the object. ALTER table OWNER TO user; would update tables alter time. Understood... a COMMENT is a database object, then if we add a creation time column to pg_description/shdescription tables how we track his creation time? Open to other thoughts on this and perhaps we should create a wiki page to start documentating the semantics. Once we get agreement there, it's just a bit of code. :) +1 Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] psql \l to accept patterns
+ | | | | | fabrizio=CTc/fabrizio (3 rows) 3) Now '\l[S+] [pattern]' works: postgres=# CREATE DATABASE fabrizio; CRECREATE DATABASE postgres=# CREATE DATABASE postgis; CREATE DATABASE postgres=# CREATE DATABASE mytemplate; CREATE DATABASE postgres=# \l fab* List of databases Name | Owner | Encoding | Collate |Ctype| Access privileges --+--+--+-+-+--- fabrizio | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (1 row) postgres=# \l post* List of databases Name | Owner | Encoding | Collate |Ctype| Access privileges --+--+--+-+-+--- postgis | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (2 rows) postgres=# \l *template* List of databases Name| Owner | Encoding | Collate |Ctype| Access privileges +--+--+-+-+--- mytemplate | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio + | | | | | fabrizio=CTc/fabrizio template1 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio + | | | | | fabrizio=CTc/fabrizio (3 rows) postgres=# \l *template List of databases Name| Owner | Encoding | Collate |Ctype| Access privileges +--+--+-+-+--- mytemplate | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (1 row) 4) By command line all works ok too... $ ./bin/psql -c \l List of databases Name| Owner | Encoding | Collate |Ctype| Access privileges +--+--+-+-+--- fabrizio | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | mytemplate | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgis| fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (3 rows) $ ./bin/psql -c \lS List of databases Name| Owner | Encoding | Collate |Ctype| Access privileges +--+--+-+-+--- fabrizio | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | mytemplate | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgis| fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio + | | | | | fabrizio=CTc/fabrizio template1 | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio + | | | | | fabrizio=CTc/fabrizio (6 rows) $ ./bin/psql -c \l post* List of databases Name | Owner | Encoding | Collate |Ctype| Access privileges --+--+--+-+-+--- postgis | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | fabrizio | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (2 rows) 5) Docs and psql help was updated correctly. The attached patch is ok for me and ready for commit. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] pg_catalog
On Mon, Jan 28, 2013 at 10:24 AM, Graham Little graham.lit...@aspone.co.uk wrote: Hi, I have tried other sources but to no avail. Could someone please tell me which tables in pg_catalog are effected by creating or dropping a trigger. If there is a work flow diagram or source code location you want to point me to rather than listing them that will be fine also. I don't know if its completely right, but when a trigger is created the following catalog are affecteds: - pg_trigger (new entry) - pg_depend (new entry) - pg_class (update relhastriggers column) Some plonker deleted some rows directly in the pg_triggers table …. I need to fix it. Maybe you simply DROP and CREATE trigger solve it... Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] CREATE SCHEMA IF NOT EXISTS
2012/8/15 David E. Wheeler da...@justatheory.com Hackers, Is there any reason not to add $subject? Would it be difficult? Looking to the source code I think this feature isn't hard to implement... I'm writing a little path to do that and I'll send soon... Would save me having to write a DO statement every time I needed it (which in migration scripts is fairly often). I understand your difficulty. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] CREATE SCHEMA IF NOT EXISTS
2012/8/15 David E. Wheeler da...@justatheory.com On Aug 15, 2012, at 11:31 AM, Fabrízio de Royes Mello wrote: Is there any reason not to add $subject? Would it be difficult? Looking to the source code I think this feature isn't hard to implement... I'm writing a little path to do that and I'll send soon... Cool, thanks! The attached patch implement this feature: CREATE SCHEMA [ IF NOT EXISTS ] schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ] CREATE SCHEMA [ IF NOT EXISTS ] AUTHORIZATION user_name [ schema_element [ ... ] ] Now, PostgreSQL don't trow an error if we use IF NOT EXISTS in CREATE SCHEMA statement. So, I don't know the next steps... Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello create_schema_if_not_exists.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] CREATE SCHEMA IF NOT EXISTS
2012/8/16 David E. Wheeler da...@justatheory.com On Aug 16, 2012, at 10:36 AM, Fabrízio de Royes Mello wrote: The attached patch implement this feature: CREATE SCHEMA [ IF NOT EXISTS ] schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ] CREATE SCHEMA [ IF NOT EXISTS ] AUTHORIZATION user_name [ schema_element [ ... ] ] Now, PostgreSQL don't trow an error if we use IF NOT EXISTS in CREATE SCHEMA statement. So, I don't know the next steps... Awesome, thanks! Please add it to the next CommitFest: https://commitfest.postgresql.org/action/commitfest_view?id=15 Patch added to CommitFest: https://commitfest.postgresql.org/action/patch_view?id=907 Thanks, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] CREATE SCHEMA IF NOT EXISTS
2012/8/16 Dickson S. Guedes lis...@guedesoft.net 2012/8/16 Fabrízio de Royes Mello fabriziome...@gmail.com: The attached patch implement this feature: CREATE SCHEMA [ IF NOT EXISTS ] schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ] CREATE SCHEMA [ IF NOT EXISTS ] AUTHORIZATION user_name [ schema_element [ ... ] ] Now, PostgreSQL don't trow an error if we use IF NOT EXISTS in CREATE SCHEMA statement. I started testing this, but I didn't see regression tests for it. Could you write them?. The attached patch contains regression tests for it. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello create_schema_if_not_exists_v2.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] CREATE SCHEMA IF NOT EXISTS
2012/8/17 Fabrízio de Royes Mello fabriziome...@gmail.com I started testing this, but I didn't see regression tests for it. Could you write them?. The attached patch contains regression tests for it. Please, don't consider de last patch (v2) because I make a little mistake on create_schema.sgml structure. The attached patch fix that. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello create_schema_if_not_exists_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] CREATE SCHEMA IF NOT EXISTS
2012/8/17 Alvaro Herrera alvhe...@2ndquadrant.com Excerpts from Fabrízio de Royes Mello's message of vie ago 17 09:16:30 -0400 2012: The attached patch contains regression tests for it. I think you're missing support in copyfuncs.c and equalfuncs.c for the new field in the node. You're completely right, thanks... The attached patch add support for the new field in the node in copyfuncs.c and equalfuncs.c. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello create_schema_if_not_exists_v4.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] CREATE SCHEMA IF NOT EXISTS
2012/8/17 Alvaro Herrera alvhe...@2ndquadrant.com Excerpts from Dickson S. Guedes's message of vie ago 17 10:37:25 -0400 2012: 2012/8/17 Fabrízio de Royes Mello fabriziome...@gmail.com: 2012/8/17 Alvaro Herrera alvhe...@2ndquadrant.com Excerpts from Fabrízio de Royes Mello's message of vie ago 17 09:16:30 -0400 2012: The attached patch contains regression tests for it. I think you're missing support in copyfuncs.c and equalfuncs.c for the new field in the node. You're completely right, thanks... The attached patch add support for the new field in the node in copyfuncs.c and equalfuncs.c. Maybe I'm missing something but shouldn't it being handled in extension.c too? Please be more explicit? I don't know what you mean. Returning conversation to the list. I think he talked about this piece of code: diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c index 8512cdb..e359a9c 100644 --- a/src/backend/commands/extension.c +++ b/src/backend/commands/extension.c @@ -1374,6 +1374,7 @@ CreateExtension(CreateExtensionStmt *stmt) csstmt-schemaname = schemaName; csstmt-authid = NULL; /* will be created by current user */ csstmt-schemaElts = NIL; + csstmt-if_not_exists = false; CreateSchemaCommand(csstmt, NULL); /* Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello create_schema_if_not_exists_v5.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] CREATE SCHEMA IF NOT EXISTS
2012/8/27 Dickson S. Guedes lis...@guedesoft.net [...] Two questions: - Should this patch implements others INEs like ADD COLUMN IF NOT EXISTS? If this feature is important I believe we must implement it. Exists several CREATE statements without IF NOT EXISTS option too, so we can discuss more about it and I can implement it in this patch or in another. - Should pg_dump or pg_restore support some kind of flag to use a CREATE SCHEMA IF NOT EXISTS ... instead CREATE SCHEMA ...? I can't imagine a use case for this feature... can you explain more about your idea? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] CREATE SCHEMA IF NOT EXISTS
2012/9/21 David E. Wheeler da...@justatheory.com
On Sep 21, 2012, at 10:59 AM, Tom Lane t...@sss.pgh.pa.us wrote:
I don't believe this has been thought through nearly carefully enough.
If CREATE SCHEMA created a schema and nothing more, then the proposed
implementation would probably be fine. But per spec, CREATE SCHEMA
can specify not only creating the schema but a whole bunch of objects
within the schema. As coded, if the schema exists then creation of
the specified sub-objects is just skipped, regardless of whether they
exist or not. I doubt that this is really sane behavior. Would the
principle of least astonishment dictate that the IF NOT EXISTS option
apply implicitly to each sub-object as well? (If so, we'd have to
extend everything that can appear in OptSchemaEltList; most of those
commands don't have IF NOT EXISTS options today.)
I had no idea about that functionality. Seems very strange.
I completely forgot this functionality. The example above is from our docs
[1]:
CREATE SCHEMA hollywood
CREATE TABLE films (title text, release date, awards text[])
CREATE VIEW winners AS
SELECT title, release FROM films WHERE awards IS NOT NULL;
The CREATE SCHEMA statement accepts another CREATE commands (CREATE
{TABLE | VIEW | INDEX | SEQUENCE | TRIGGER}), and the current patch do not
consider this options.
A possible compromise is to allow the IF NOT EXISTS option only without
a schema-element list, which I suspect is the only use-case David had in
mind to start with anyway.
Yes, true.
Ok.
The existing patch added the check in a pretty randomly chosen spot too,
with one bad consequence being that if the schema already exists then it
will fall out with the wrong user ID in effect, creating a security bug.
But I'm not entirely sure where to put the check instead. Should we put
it before or after the permissions checks --- that is, should IF NOT
EXISTS require that you would have had permission to create the schema?
Or, if the schema does exist, should we just call it good anyway? I'm
too lazy to look at how other INE options resolved this question, but it
seems like we ought to be consistent.
Agreed. But if it already exists, where does it currently die? ISTM that
would be the point to check, if possible.
I change the patch (attached) to skip only the schema creation and execute
others statements...
Also, the AUTHORIZATION clause of CREATE SCHEMA creates an aspect of all
this that doesn't exist for any other kind of CREATE command, namely
that the object might have been requested to be created under some other
user id. For instance, supposing that we were to go forward with trying
to create sub-objects, but the ownership of the existing schema is
different from what's implied or specified by CREATE SCHEMA, should the
sub-objects be (attempted to be) created as owned by that user instead?
Perhaps not, but I'm not at all sure.
I tend to think that if the schema exists, there should be no attempt to
create the sub-objects. Seems the least astonishing to me.
Why don't create sub-objects? I think the INE clause must affect only
CREATE SCHEMA statement, the others must be executed normally. We can
discuss more about it...
[1] http://www.postgresql.org/docs/9.2/interactive/sql-createschema.html
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
create_schema_if_not_exists_v6.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] CREATE SCHEMA IF NOT EXISTS
2012/10/2 Alvaro Herrera alvhe...@2ndquadrant.com Excerpts from Andrew Dunstan's message of mar oct 02 17:24:38 -0300 2012: On 10/02/2012 03:48 PM, Tom Lane wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Well, if that's the rationale then you end up with no schema foo at all (i.e. both die), which seems even more surprising (though I admit it has the advantage of being a simple rule to document.) I think we should just disallow putting any contained objects in the statement when IF NOT EXISTS is used. It's simple to understand, simple to document and implement, and I think it covers all the sane use-cases anyway. I thought we'd already agreed on this. Well, it's not what the latest proposed patch implements. You're right... the latest proposed patch don't implements it. I'll change the patch and send soon... Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] CREATE SCHEMA IF NOT EXISTS
2012/10/2 Fabrízio de Royes Mello fabriziome...@gmail.com You're right... the latest proposed patch don't implements it. I'll change the patch and send soon... What is more reasonable? * show a syntax error or * show a message that you can not use the INE with contained objects Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] CREATE SCHEMA IF NOT EXISTS
2012/10/3 Alvaro Herrera alvhe...@2ndquadrant.com Excerpts from Fabrízio de Royes Mello's message of mié oct 03 09:27:41 -0300 2012: 2012/10/2 Fabrízio de Royes Mello fabriziome...@gmail.com You're right... the latest proposed patch don't implements it. I'll change the patch and send soon... What is more reasonable? * show a syntax error or * show a message that you can not use the INE with contained objects Second one. Maybe something like this? ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg(IF NOT EXISTS cannot be used with schema elements), parser_errposition(@9))); -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] CREATE SCHEMA IF NOT EXISTS
2012/10/3 Alvaro Herrera alvhe...@2ndquadrant.com Excerpts from Fabrízio de Royes Mello's message of mié oct 03 10:11:03 -0300 2012: Maybe something like this? ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg(IF NOT EXISTS cannot be used with schema elements), parser_errposition(@9))); Seems reasonable, but where? Please submit a complete patch. The attached patch implements the behavior we've discussed. If we use IF NOT EXISTS with schema elements then occurs an error like this: [local]:5432 fabrizio@fabrizio=# CREATE SCHEMA IF NOT EXISTS test_schema_1 CREATE TABLE abc ( a serial, b int UNIQUE ); ERROR: IF NOT EXISTS cannot be used with schema elements LINE 1: CREATE SCHEMA IF NOT EXISTS test_schema_1 ^ Time: 0,773 ms Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello create_schema_if_not_exists_v7.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] Adding comments for system table/column names
2012/10/12 Bruce Momjian br...@momjian.us There was a thread in January of 2012 where we discussed the idea of pulling system table/column name descriptions from the SGML docs and creating SQL comments for them: http://archives.postgresql.org/pgsql-hackers/2012-01/msg00837.php Magnus didn't seem to like the idea: http://archives.postgresql.org/pgsql-hackers/2012-01/msg00848.php Well, I'd expect some of those columns to get (at least over time) significantly more detailed information than they have now. Certainly more than you'd put in comments in the catalogs. And having some sort of combination there seems to overcomplicate things... I think the idea of having the short descriptions in SQL and longer ones in SGML is not maintainable. One idea would be to clip the SQL description to be no longer than a specified number of characters, with proper word break detection. Here is how psql displays column and table comments: test= create table test(x int); CREATE TABLE test= comment on column test.x IS 'wow'; COMMENT test= \d+ test Table public.test Column | Type | Modifiers | Storage | Stats target | Description +-+---+-+--+- x | integer | | plain | | wow Has OIDs: no test= comment on table test is 'yikes'; COMMENT test= \d+ List of relations Schema | Name | Type | Owner | Size | Description +--+---+--+-+- public | test | table | postgres | 0 bytes | yikes (1 row) Should I continue working on this patch? Hi all, If it can help, Euler wrote about that some time ago in your blog [1]. Regards, [1] http://eulerto.blogspot.com.br/2010/11/comment-on-catalog-tables.html -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] proposal or just idea for psql - show first N rows from relation backslash statement
On Wed, Feb 13, 2013 at 6:07 PM, Pavel Stehule pavel.steh...@gmail.comwrote: Hello probably one from my top ten SQL statement will be SELECT * FROM some_relation LIMIT 10 what do you thinking about creating special statement for this purpose? possible syntax -- ViewTable \vt table_name [rows] or \sample table_name [rows] a implementation with autocomplete is terrible simple I liked this idea, but thinking better we can implement a way to users create your own meta-commands to run: * another meta commands (like an alias) * SRFs * arbitrary SQLs All of them must accept arguments... some like this: \mset vt :table :rows 'select * from :table limit :rows' Then we can do exactly what you need: \vt foo 10 \unset vt I don't know if it's a desired feature but I would love if it exists. Best regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Call for Google Summer of Code mentors, admins
On Thu, Feb 14, 2013 at 4:02 PM, Josh Berkus j...@agliodbs.com wrote: [...] - Please suggest project ideas for GSOC - Students seeing this -- please speak up if you have projects you plan to submit. I would like to propose implement a way to track creation times to database objects. This was discussed before in this thread [1]. This was discussed before in this thread [1] but we don't reach a consensus of what we'll do, so I propose we discuss any more about it and I can implement it in GSOC2013, if my proposal will be accepted. Regards, [1] http://www.postgresql.org/message-id/CAFcNs+qMGbLmeUOnjmbna_K7=up817bpw9qxhbctgnscpkv...@mail.gmail.com -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Call for Google Summer of Code mentors, admins
On Tue, Feb 19, 2013 at 5:38 PM, Josh Berkus j...@agliodbs.com wrote: This was discussed before in this thread [1] but we don't reach a consensus of what we'll do, so I propose we discuss any more about it and I can implement it in GSOC2013, if my proposal will be accepted. As a mentor or as a student? As a student. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Call for Google Summer of Code mentors, admins
On Tue, Feb 19, 2013 at 5:43 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 19.02.2013 20:07, Fabrízio de Royes Mello wrote: I would like to propose implement a way to track creation times to database objects. This was discussed before in this thread [1]. This was discussed before in this thread [1] but we don't reach a consensus of what we'll do, so I propose we discuss any more about it and I can implement it in GSOC2013, if my proposal will be accepted. I don't think that's a good GSoC project. There's no consensus on what to do, if anything, so 95% of the work is going to arguing over what we want, and 5% coding. I'd suggest finding something more well-defined. You all right about we don't have no consensus on what to do, but maybe this can be a opportunity to we do that. I know a lot of people (friends, customers, users, ...) who would love to have this feature in future versions of PostgreSQL. Anyway there is another well defined feature which you recommend to a good GSoC project? Best regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] [GENERAL] currval and DISCARD ALL
On Tue, Apr 16, 2013 at 6:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: [...] Or, if you'd rather a more direct answer: wanting this sounds like evidence of bad application design. Why is your app dependent on getting failures from currval, and isn't there a better way to do it? The sequence cache (seqtab) is used per each backend, so if we use a connection pooler (like pgbouncer in session mode) between our app and postgres we can get a wrong value from 'currval' because the backend isn't completely clean. This isn't it a good reason to implement this feature? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
[HACKERS] Fix typo in contrib/hstore/crc32.c comment
Hi all, The attached patch fix a little typo on contrib/hstore/crc32.c comment. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello fix_typo_hstore_crc32_comment.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] [GENERAL] currval and DISCARD ALL
On Fri, Apr 19, 2013 at 10:50 AM, Robert Haas robertmh...@gmail.com wrote: [...] So it seems to me that we pretty much already made a decision that the controlling definition of DISCARD ALL is that, as the fine manual says DISCARD ALL resets a session to its original state. Whatever decision we make now ought to be consistent with that. IOW, I don't care whether we introduce a new subcommand or not. But I *do* think that that we ought to make our best effort to have DISCARD ALL clear everything that smells like session-local state. Random incompatibilities between what you see when running under a connection pooler and what you see when connecting the DB directly are *bad*, regardless of whether a well-designed application should be relying on those particular things or not. The whole point of having a transparent connection pooler is that it's supposed to be transparent to the application. +1 The attached wip patch do that and introduce a subcommand 'SEQUENCES', but if we decide to don't add a new subcommand to DISCARD, then its easier to modify the patch. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello discard_sequences.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] [GENERAL] currval and DISCARD ALL
On Fri, Apr 19, 2013 at 11:12 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Apr 19, 2013 at 10:05 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: The attached wip patch do that and introduce a subcommand 'SEQUENCES', but if we decide to don't add a new subcommand to DISCARD, then its easier to modify the patch. This patch is quite wrong. It frees seqtab without clearing the pointer, so the next reference will stomp on memory that may have been reallocated. And it doesn't even free seqtab correctly, since it only frees the first node in the linked list. Ohh sorry... you're all right... I completely forgot to finish the ReleaseSequenceCaches to transverse 'seqtab' linked list and free each node. The attached patch have this correct code. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello discard_sequences.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] Proposal to add --single-row to psql
On Tue, Apr 23, 2013 at 1:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Isn't there already a way to set FETCH_COUNT from the command line? (ie, I think there's a generic variable-assignment facility that could do this) Christopher, Tom is all right... from psql [1] command line we can do that: $ bin/psql --variable=FETCH_COUNT=100 psql (9.3devel) Type help for help. fabrizio=# \echo :FETCH_COUNT 100 Regards, [1] http://www.postgresql.org/docs/current/interactive/app-psql.html#AEN84903 -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] pg_controldata gobbledygook
On Fri, Apr 26, 2013 at 12:22 AM, Peter Geoghegan p...@heroku.com wrote: On Thu, Apr 25, 2013 at 8:07 PM, Peter Eisentraut pete...@gmx.net wrote: Comments? +1 from me. I don't think that these particular changes would break WAL-E, Heroku's continuous archiving tool, which has a class called PgControlDataParser. However, it's possible to imagine someone being affected in a similar way. So I'd be sure to document it clearly, and to perhaps preserve the old label names to avoid breaking scripts. Why don't we add options to pg_controldata outputs the info in other several formats like json, yaml, xml or another one? Best regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
[HACKERS] Patch to .gitignore
Hi all, The proposed patch add some files to ignore in .gitignore: - tags (produced by src/tools/make_ctags) - TAGS (produced bu src/tools/make_etags) - .*.swp (may appear in source tree if vi/vim was killed by some reason) Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello gitignore_ctags_etags_vi.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] Patch to .gitignore
On Fri, May 24, 2013 at 12:04 AM, Christopher Browne cbbro...@gmail.comwrote: There hasn't been general agreement on the merits of particular .gitignore rules of this sort. You could hide your own favorite patterns by putting this into your ~/.gitignore that isn't part of the repo, configuring this globally, thus: git config --global core.excludesfile '~/.gitignore' Yes... I know that... That has the consequence that you can hide whatever things your own tools like to create, and not worry about others' preferences. Us Emacs users can put things like *~, #*#, and such into our own ignore configuration; that doesn't need to bother you, and vice-versa for your vim-oriented patterns. I agree with you about vim-oriented patterns, because its a particular tool, but ctags and etags be part of postgres source tree and its generate some output inside them, so I think we must ignore it. IMHO all output generated by tools inside the source tree that will not be committed must be added to .gitignore Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
[HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements
Hi all,
I working in a patch to include support of IF NOT EXISTS into CREATE
statements that not have it yet.
I started with DefineStmt section from src/backend/parser/gram.y:
- CREATE AGGREGATE [ IF NOT EXISTS ] ...
- CREATE OPERATOR [ IF NOT EXISTS ] ...
- CREATE TYPE [ IF NOT EXISTS ] ... [AS [{ENUM | RANGE}] (...)]
- CREATE TEXT SEARCH {PARSER | DITIONARY | TEMPLATE | CONFIGURATION} [ IF
NOT EXISTS ] ...
- CREATE COLLATION [ IF NOT EXISTS ] ...
My intention is cover anothers CREATE statements too, not just the above.
If has no objection about this implementation I'll finish him and soon I
sent the patch.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements
On Wed, Jun 12, 2013 at 4:00 PM, Peter Eisentraut pete...@gmx.net wrote:
I'm wondering where IF NOT EXISTS and OR REPLACE will meet.
For example, why doesn't your list include CREATE FUNCTION?
I have on my personal todo list to add OR REPLACE support to CREATE
AGGREGATE and CREATE OPERATOR. They are kind of like functions, after
all, and CREATE OR REPLACE FUNCTION is clearly widely useful.
I suppose both could be useful, but if we're going to make sweeping
changes, perhaps that should be clarified.
I did not include CREATE FUNCTION precisely because I had the same doubts.
IMO the IF NOT EXISTS and OR REPLACE are differents, and can coexists in
the same statements but not used at the same time:
CREATE [ OF REPLACE | IF NOT EXISTS ] FUNCTION ...
I can use IF NOT EXISTS to CREATE a {FUNCTION | AGGREGATE | OPERATOR}
without replace (OR REPLACE) its definition to just create missing objects
and don't
raise an exception if already exists.
Btw., I also want REPLACE BUT DO NOT CREATE.
Can you explain more about it?
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements
On Mon, Jun 17, 2013 at 12:36 AM, Robins Tharakan thara...@gmail.comwrote: Hi, Did some basic checks on this patch. List-wise feedback below. [...] Dear Robins, Thanks for your review. I attach your considerations to Commit Fest [1]. Regards, [1] https://commitfest.postgresql.org/action/patch_view?id=1133 -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements
On Mon, Jun 17, 2013 at 11:33 PM, Peter Eisentraut pete...@gmx.net wrote: Replace/alter the object if it already exists, but fail if it does not exist. The complete set of variants is: - object does not exist: - proceed (normal CREATE) - error (my above description) - object exists: - replace (CREATE OR REPLACE) - skip (CREATE IF NOT EXISTS) - error (normal CREATE) I understood. The syntax can be like that? - CREATE [ OR REPLACE | IF NOT EXISTS ] AGGREGATE ... - CREATE [ OR REPLACE | IF NOT EXISTS ] OPERATOR ... - CREATE [ OR REPLACE | IF NOT EXISTS ] FUNCTION ... I can add this features too, but IMHO it is more prudent at this CF we just implement the IF NOT EXISTS according the initial proposal. I'm planning another patch do next CF to add support to IF NOT EXISTS to others CREATE statements. See my planning [1]. Regards, [1] https://docs.google.com/spreadsheet/ccc?key=0Ai7oCVcVQiKFdEctQUxNNlR1R2xRTUpJNFNDcFo4MUEusp=sharing -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements
On Thu, Jun 20, 2013 at 1:52 AM, Amit Langote amitlangot...@gmail.com wrote: Is it possible to: CREATE [ OR REPLACE | IF NOT EXISTS ] OPERATOR CLASS I am in a situation where I need to conditionally create an operator class (that is, create only if already does not exist). [...] The intention is cover all CREATE OPERATOR variants. See my planning [1]. Regards, [1] https://docs.google.com/spreadsheet/ccc?key=0Ai7oCVcVQiKFdEctQUxNNlR1R2xRTUpJNFNDcFo4MUEusp=sharing -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] [PATCH] add long options to pgbench (submission 1)
On Thu, Jun 20, 2013 at 9:59 AM, Fabien COELHO coe...@cri.ensmp.fr wrote: Please fix that and re-send the patch. Find attached diff wrt current master. Thanks. -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements
On Thu, Jun 20, 2013 at 1:24 PM, Peter Eisentraut pete...@gmx.net wrote: On 6/20/13 11:04 AM, Robert Haas wrote: I kind of don't see the point of having IF NOT EXISTS for things that have OR REPLACE, and am generally in favor of implementing OR REPLACE rather than IF NOT EXISTS where possible. I tend to agree. I agree if is possible to have OR REPLACE then we must do that, but in other hands I don't see a problem if we have support to both IF NOT EXISTS and OR REPLACE. In some cases we don't really want to replace the object body if its already exists so IF NOT EXISTS is useful to don't break the transaction inside a upgrade script. Btw., I also want REPLACE BUT DO NOT CREATE. That's a mouthful. What's it good for? If you run an upgrade SQL script that is supposed to replace, say, a bunch of functions with new versions, you'd want the behavior that it replaces the existing function if it exists, but errors out if it doesn't, because then you're perhaps connected to the wrong database. It's a marginal feature, and I'm not going to pursue it, but if someone wanted to make the CREATE commands fully featured, there is use for this. Well, my intention is do that for all CREATE commands. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements
On Mon, Jun 24, 2013 at 8:05 AM, Andres Freund and...@2ndquadrant.com wrote: I'd argue if we go that way - which seems to be a good idea - we really ought to make a complete pass and add it to all commands where it's currently missing. Yeah... this is my purpose, but I decide do that in two steps. First with the patch already sent to CF1 and second with another patch to cover the remaining CREATE commands. I created a simple spreadsheet [1] to control my work. Suggestions are welcome. * CREATE DOMAIN * CREATE GROUP * CREATE TABLE AS * CREATE MATERIALIZED VIEW * CREATE SEQUENCE (we have ALTER but not CREATE?) * CREATE TABLESPACE (arguably slightly harder) * CREATE FOREIGN DATA WRAPPER * CREATE SERVER * CREATE DATABASE * CREATE USER MAPPING * CREATE TRIGGER * CREATE EVENT TRIGGER * CREATE INDEX * CLUSTER Ok. Cases that seem useful, even though we have OR REPLACE: * CREATE VIEW * CREATE FUNCTION +1 Of dubious use: * CREATE OPERATOR CLASS * CREATE OPERATOR FAMILY * CREATE RULE * CREATE CONVERSION In fact I would say that will be seldom used, but I don't see any problem to implement them. Regards, [1] https://docs.google.com/spreadsheet/ccc?key=0Ai7oCVcVQiKFdEctQUxNNlR1R2xRTUpJNFNDcFo4MUEusp=sharing -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
[HACKERS] Add /coverage/ to .gitignore
Hi all, When we run... ./configure --enable-converage make coverage-html ...the output is generated into /coverage/ directory. The attached patch add /converage/ to .gitignore. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello gitignore-coverage.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] Add regression tests for DISCARD
On Mon, Jul 1, 2013 at 5:59 PM, Robins Tharakan thara...@gmail.com wrote: Thanks Marko for pointing out about guc.sql. Please find attached a patch to move DISCARD related tests from guc.sql to discard.sql. It adds an extra test for a DISCARD PLANS line, although I amn't sure on how to validate that its working. Personally, I wouldn't call this a great patch, since most of the tests were already running, although in a generic script. The separation of DISCARD related tests to another file is arguably good for the long-term though. Robins, You must add this new test case called discard to src/test/regress/parallel_schedule and src/test/regress/serial_schedule, because if we do make check the new discard test case is not executed. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Add regression tests for DISCARD
On Sun, Jul 7, 2013 at 12:49 AM, Robins Tharakan thara...@gmail.com wrote: Thanks Fabrizio. Although parallel_schedule was a miss for this specific patch, however, I guess I seem to have missed out serial_schedule completely (in all patches) and then thanks for pointing this out. Subsequently Robert too noticed the miss at the serial_schedule end. Please find attached a patch, updated towards serial_schedule / parallel_schedule as well as the role name change as per Robert's feedback on CREATE OPERATOR thread. Ok. Some basic checks on this patch: - Removed unnecessary extra-lines: Yes - Cleanly applies to Git-Head: Yes - Documentation Updated: N/A - Tests Updated: Yes - All tests pass: Yes. - Does it Work: Yes - Do we want it?: Yes - Is this a new feature: No - Does it support pg_dump: No - Does it follow coding guidelines: Yes - Any visible issues: No - Any corner cases missed out: No - Performance tests required: No - Any compiler warnings: No - Are comments sufficient: Yes - Others: N/A Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] [GENERAL] currval and DISCARD ALL
On 25-07-2013 05:32, suresh.balasubra wrote:
Disclaimer: I am no hacker, just a PostGreSQL user, trying to provide a user
scenario where DISCARD SEQUENCES functionality is required.
We have designed a developed a small Application Development platform for
which the backend is PostGreSQL.
There is a DBLayer which is responsible in generating SQL statements for all
the INSERT, UPDATE, DELETE operations. Data can be pushed into multiple
tables using this layer. What we provide to this layer is just a DataSet
(.NET). DataTables in the DataSet will be named after their respective
tables. We also use DataColumn extended properties to push in additional
logic. All these happen with in a transaction and also in one shot, just one
hit to the DB by appending SQL statements in proper order.
There is an interesting feature that we have built into this DBlayer which
is auto linking. All tables in our system will have a serial field 'id'.
Suppose there is a master table (let us call it 'voucher') and a detail
table ('voucher_lines'), and we are using the layer to push one record to
the master table and 50 records to the detail table. 'voucher_lines' table
will have a integer column 'voucher_id'. '*_id' fields are automatically
populated with 'currval('*_id_seq'). All this works like a charm.
Now, imagine we want to push data into another table (say 'invoice') which
also has a field 'voucher_id'. This is a different activity not connected
with the above mentioned transaction. In this scenario this field will get
updated as currval('voucher_id_seq') returns a value. But we do not want
that to be updated. What we want is to resolve '*_id' fields into values
only within a transaction. After the transaction we want to get away with
the session. If we could have cleared the session someway (DISCARD ALL does
it, but not the currval sequence info) after the first transaction it would
have worked for us.
I already sent a patch to implement DISCARD SEQUENCES [1] that I expect
to be part of 9.4 version.
Regards,
[1] https://commitfest.postgresql.org/action/patch_view?id=1171
--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
--
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] [GENERAL] currval and DISCARD ALL
On 19-08-2013 16:10, Boszormenyi Zoltan wrote:
I am reviewing your patch.
Thanks...
* Is the patch in a patch format which has context? (eg: context diff
format)
Yes.
* Does it apply cleanly to the current git master?
Almost. No rejects, no fuzz, only offset for some files.
* Does it include reasonable tests, necessary doc patches, etc?
Documentation, yes. Tests, no.
* Does the patch actually implement what it's supposed to do?
Yes.
* Do we want that?
Yes.
* Do we already have it?
No.
* Does it follow SQL spec, or the community-agreed behavior?
The SQL standard doesn't have DISCARD.
Otherwise the behaviour is obvious.
* Does it include pg_dump support (if applicable)?
n/a
* Are there dangers?
It changes applications' assumptions slightly but takes the
behaviour closer to the wording of the documentation.
* Have all the bases been covered?
Yes.
* Does the feature work as advertised?
Yes.
* Are there corner cases the author has failed to consider?
No.
* Are there any assertion failures or crashes?
No.
* Does the patch slow down simple tests?
No.
* If it claims to improve performance, does it?
n/a
* Does it slow down other things?
No.
* Does it follow the project coding guidelines?
Yes.
Maybe a little stylistic comment:
+void
+ReleaseSequenceCaches()
+{
+ SeqTableData *ptr = seqtab;
+ SeqTableData *tmp = NULL;
+
+ while (ptr != NULL)
+ {
+ tmp = ptr;
+ ptr = ptr-next;
+ free(tmp);
+ }
+
+ seqtab = NULL;
+}
I would rename the variables to seq and next from
ptr and tmp, respectively, to make them even more
obvious. This looks a little better:
+void
+ReleaseSequenceCaches()
+{
+ SeqTableData *seq = seqtab;
+ SeqTableData *next;
+
+ while (seq)
+ {
+ next = seq-next;
+ free(seq);
+ seq = next;
+ }
+
+ seqtab = NULL;
+}
Done!
* Are there portability issues?
No.
* Will it work on Windows/BSD etc?
It should. There are no extra system calls.
* Are the comments sufficient and accurate?
The feature needs very little code which is downright obvious.
There are no comments but I don't think the code needs it.
* Does it do what it says, correctly?
Yes.
I lied.
There is one little problem. There is no command tag
reported for DISCARD SEQUENCES:
zozo=# create sequence s1;
CREATE SEQUENCE
zozo=# select nextval('s1');
nextval
-
1
(1 row)
zozo=# select currval('s1');
currval
-
1
(1 row)
zozo=# discard all;
DISCARD ALL
zozo=# discard sequences;
???
zozo=# select currval('s1');
ERROR: currval of sequence s1 is not yet defined in this session
Fixed!
* Does it produce compiler warnings?
Only one:
src/backend/commands/sequence.c should have
#include commands/sequence.h
because of this:
sequence.c:1608:1: warning: no previous prototype for
‘ReleaseSequenceCaches’ [-Wmissing-prototypes]
ReleaseSequenceCaches()
^
Fixed!
* Can you make it crash?
No.
* Is everything done in a way that fits together coherently with other
features/modules?
Yes.
* Are there interdependencies that can cause problems?
I don't think so.
The attached patch fix the items reviewed by you.
Regards,
--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
diff --git a/doc/src/sgml/ref/discard.sgml b/doc/src/sgml/ref/discard.sgml
index 65ebbae..abd3e28 100644
--- a/doc/src/sgml/ref/discard.sgml
+++ b/doc/src/sgml/ref/discard.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
refsynopsisdiv
synopsis
-DISCARD { ALL | PLANS | TEMPORARY | TEMP }
+DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }
/synopsis
/refsynopsisdiv
@@ -67,6 +67,15 @@ DISCARD { ALL | PLANS | TEMPORARY | TEMP }
/varlistentry
varlistentry
+termliteralSEQUENCES/literal/term
+listitem
+ para
+ Releases all internally cached sequences.
+ /para
+/listitem
+ /varlistentry
+
+ varlistentry
termliteralALL/literal/term
listitem
para
@@ -83,6 +92,7 @@ UNLISTEN *;
SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD TEMP;
+DISCARD SEQUENCES;
/programlisting/para
/listitem
/varlistentry
diff --git a/src/backend/commands/discard.c b/src/backend/commands/discard.c
index 76f3ab6..f4e7e06 100644
--- a/src/backend/commands/discard.c
+++ b/src/backend/commands/discard.c
@@ -18,13 +18,14 @@
#include commands/async.h
#include commands/discard.h
#include commands/prepare.h
+#include commands/sequence.h
#include utils/guc.h
#include utils/portal.h
static void DiscardAll(bool isTopLevel);
/*
- * DISCARD { ALL | TEMP | PLANS }
+ * DISCARD { ALL | SEQUENCES | TEMP | PLANS }
*/
void
DiscardCommand(DiscardStmt *stmt, bool isTopLevel)
@@ -39,6 +40,10 @@ DiscardCommand(DiscardStmt *stmt, bool isTopLevel)
ResetPlanCache();
break;
+ case
Re: [HACKERS] [GENERAL] currval and DISCARD ALL
On Thu, Oct 3, 2013 at 5:26 PM, Robert Haas robertmh...@gmail.com wrote: Committed with assorted revisions. In particular, I renamed the function that discards cached sequence data, revised the wording of the documentation, added a regression test, and tweaked the list-free code to pop items off one after the other instead of walking the list and then NULLing it out at the end. Although no ERROR is possible here currently, this coding style is generally preferable because it's robust against being interrupted in the middle. Thanks! -- Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Re: [HACKERS] FDW API / flow charts for the docs?
On Wed, Oct 16, 2013 at 8:35 PM, Tomas Vondra t...@fuzzy.cz wrote: [...] Attached is the set of flow charts, showing the sequence of callbacks for all the supported commands (i.e. SELECT, INSERT, UPDATE, DELETE, ANALYZE). Thank you very much... this flow charts will help many people, including me ;-) Wouldn't it be useful to put something like this into the docs? I mean, the FDW API is not going to get any simpler, and for me this was a significant help. +1 to add into docs. I think we can add this flow charts to [1]. Regards, [1] http://www.postgresql.org/docs/9.3/interactive/fdwhandler.html -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
[HACKERS] Time-Delayed Standbys
Hi all, The attached patch is a continuation of Robert's work [1]. I made some changes: - use of Latches instead of pg_usleep, so we don't have to wakeup regularly. - call HandleStartupProcInterrupts() before CheckForStandbyTrigger() because might change the trigger file's location - compute recoveryUntilDelayTime in XLOG_XACT_COMMIT and XLOG_XACT_COMMIT_COMPACT checks - don't care about clockdrift because it's an admin problem. Regards, [1] http://www.postgresql.org/message-id/BANLkTi==ttzhdqwzwjdjmof__8yua7l...@mail.gmail.com -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello time-delayed-standby-v1.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] pg_dump and pg_dumpall in real life
On Mon, Nov 11, 2013 at 8:20 PM, Josh Berkus j...@agliodbs.com wrote: [...] Well, then we just need pg_restore to handle the role already exists error message gracefully. That's all. Or a CREATE ROLE IF NOT EXISTS statement, and use that for roles. I'm working in a patch to add IF NOT EXISTS for all CREATE statements, including of course the CREATE ROLE statement. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] -d option for pg_isready is broken
On Wed, Nov 13, 2013 at 9:37 PM, Josh Berkus j...@agliodbs.com wrote:
handyrep@john:~/handyrep$ pg_isready --version
pg_isready (PostgreSQL) 9.3.1
handyrep@john:~/handyrep$ pg_isready -h john -p 5432 -U postgres -d
postgres -q
pg_isready: missing = after postgres in connection info string
handyrep@john:~/handyrep$ pg_isready --host=john --port=5432
--user=postgres --dbname=postgres
pg_isready: missing = after postgres in connection info string
handyrep@john:~/handyrep$ pg_isready -h john -p 5432 -U postgres
john:5432 - accepting connections
so apparently the -d option:
a) doesn't work, and
b) doesn't do anything
I suggest simply removing it from the utility.
I'll note that the -U option doesn't appear to do anything relevant
either, but at least it doesn't error unnecessarily:
handyrep@john:~/handyrep$ pg_isready -h john -p 5432 -U no_such_user
john:5432 - accepting connections
The attached patch fix it.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Timbira: http://www.timbira.com.br
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
diff --git a/src/bin/scripts/pg_isready.c b/src/bin/scripts/pg_isready.c
index d27ccea..7568df5 100644
--- a/src/bin/scripts/pg_isready.c
+++ b/src/bin/scripts/pg_isready.c
@@ -130,7 +130,7 @@ main(int argc, char **argv)
/*
* Get the host and port so we can display them in our output
*/
- if (pgdbname)
+ if (pgdbname strchr(pgdbname, '=') != NULL)
{
opts = PQconninfoParse(pgdbname, errmsg);
if (opts == NULL)
--
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] CREATE TABLE IF NOT EXISTS AS
On Sun, Nov 17, 2013 at 6:05 PM, David E. Wheeler da...@justatheory.comwrote: On Nov 16, 2013, at 4:40 PM, Tom Lane t...@sss.pgh.pa.us wrote: Co-worker asked a question I could not answer: Why is IF NOT EXISTS not supported by CREATE TABLE AS? That's an even worse idea than plain CREATE IF NOT EXISTS (which was put in over vocal objections from me and some other people). Not only would you not have the faintest clue as to the properties of the table afterwards, but no clue as to its contents either. You mean that, after running it, one cannot tell whether or not a new table was created or not without looking at it? I guess that makes sense, though sometimes I like to tell the system to assume that I know what I’m doing -- e.g., that either outcome works for me. Not essential as a core feature, mind you; I can use DO to accomplish the same thing. It’s just a bit more work that way. And perhaps that’s for the best. I'm planning to implement it for the next commit fest (2014-01)... Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] CREATE TABLE IF NOT EXISTS AS
On Wed, Nov 20, 2013 at 2:10 AM, Peter Eisentraut pete...@gmx.net wrote: On Mon, 2013-11-18 at 11:16 -0200, Fabrízio de Royes Mello wrote: I'm planning to implement it for the next commit fest (2014-01)... This email was registered in the commit fest as a patch, but contains no patch and you indicate that it belongs in different commit fest. Please sort that out. Sorry, I registered the wrong message-id. Fixed. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
[HACKERS] [PATCH] Store Extension Options
Hi all, The main goal of this patch is enable to an user the capability to store options (relations and attributes) related to extensions by using a fixed prefix called 'ext' in the defined name. It's cant be useful for replication solutions. So, with this patch we can do that: ALTER TABLE foo SET (ext.somext.do_replicate=true); When 'ext' is the fixed prefix, 'somext' is the extension name, 'do_replicate' is the extension option and 'true' is the value. Also we can use this form to define storage options to indexes and per-attribute options. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello diff --git a/doc/src/sgml/ref/alter_index.sgml b/doc/src/sgml/ref/alter_index.sgml index d210077..bf4e196 100644 --- a/doc/src/sgml/ref/alter_index.sgml +++ b/doc/src/sgml/ref/alter_index.sgml @@ -82,6 +82,15 @@ ALTER INDEX [ IF EXISTS ] replaceable class=PARAMETERname/replaceable RESE xref linkend=SQL-REINDEX to get the desired effects. /para + note + para + A special prefix called 'replaceable class=PARAMETERext./' can be + used to define storage parameter. The storage parameters with this prefix + will be used by 'extensions'. Storage option nomenclature: ext.name.option=value + (ext=fixed prefix, name=extension name, option=option name and value=option value). + See example bellow. + /para + /note /listitem /varlistentry @@ -202,6 +211,17 @@ ALTER INDEX distributors SET (fillfactor = 75); REINDEX INDEX distributors; /programlisting/para + para + To set a storage parameter to be used by extensions: +programlisting +ALTER INDEX distributors + SET (ext.somext.do_replicate=true); +/programlisting + (ext=fixed prefix, somext=extension name, do_replicate=option name and + true=option value) +/para + + /refsect1 refsect1 diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 89649a2..4756a58 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -213,6 +213,17 @@ ALTER TABLE [ IF EXISTS ] replaceable class=PARAMETERname/replaceable of statistics by the productnamePostgreSQL/productname query planner, refer to xref linkend=planner-stats. /para + + note + para + A special prefix called 'replaceable class=PARAMETERext./' can be used to + define per-attribute options. The attribute options with this prefix will be + used by 'extensions'. The attribute option nomenclature: ext.name.option=value + (ext=fixed prefix, name=extension name, option=option name and value=option value). + See the example bellow. + /para + /note + /listitem /varlistentry @@ -476,6 +487,11 @@ ALTER TABLE [ IF EXISTS ] replaceable class=PARAMETERname/replaceable commandALTER TABLE/ does not treat literalOIDS/ as a storage parameter. Instead use the literalSET WITH OIDS/ and literalSET WITHOUT OIDS/ forms to change OID status. + A special prefix called 'replaceable class=PARAMETERext./' can be + used to define storage parameter. The storage parameters with this prefix + will be used by 'extensions'. Storage option nomenclature: ext.name.option=value + (ext=fixed prefix, name=extension name, option=option name and value=option value). + See example bellow. /para /note /listitem @@ -1112,6 +1128,26 @@ ALTER TABLE distributors DROP CONSTRAINT distributors_pkey, ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx; /programlisting/para + para + To set a per-attribute option to be used by extensions: +programlisting +ALTER TABLE distributors + ALTER COLUMN dist_id SET (ext.somext.do_replicate=true); +/programlisting + (ext=fixed prefix, somext=extension name, do_replicate=option name and + true=option value) +/para + + para + To set a storage parameter to be used by extensions: +programlisting +ALTER TABLE distributors + SET (ext.somext.do_replicate=true); +/programlisting + (ext=fixed prefix, somext=extension name, do_replicate=option name and + true=option value) +/para + /refsect1 refsect1 diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c index b5fd30a..06c2b3a 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -275,6 +275,8 @@ static void initialize_reloptions(void); static void parse_one_reloption(relopt_value *option, char *text_str, int text_len, bool validate); +static bool is_extension_namespace(char *namespace); + /* * initialize_reloptions * initialization routine, must be called before parsing @@ -602,13 +604,15 @@ transformRelOptions(Datum oldOptions
Re: [HACKERS] [PATCH] Store Extension Options
On Thu, Nov 21, 2013 at 12:05 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Nov 20, 2013 at 1:52 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: The main goal of this patch is enable to an user the capability to store options (relations and attributes) related to extensions by using a fixed prefix called 'ext' in the defined name. It's cant be useful for replication solutions. So, with this patch we can do that: ALTER TABLE foo SET (ext.somext.do_replicate=true); When 'ext' is the fixed prefix, 'somext' is the extension name, 'do_replicate' is the extension option and 'true' is the value. This doesn't seem like a particular good choice of syntax; What's your syntax suggestion? and I also have my doubts about the usefulness of the feature. This feature can be used for replication solutions, but also can be used for any extension that need do some specific configuration about tables, attributes and/or indexes. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] psql should show disabled internal triggers
On Fri, Oct 25, 2013 at 3:37 PM, fabriziomello fabriziome...@gmail.com wrote: On 2013-09-18 15:15:55 +0200, Bernd Helmle wrote: On 2013-09-18 15:15:55 +0200, Bernd Helmle wrote: --On 18. September 2013 13:52:29 +0200 Andres Freund lt;andres@gt; wrote: If you do ALTER TABLE ... DISABLE TRIGGER ALL; and then individually re-enable the disabled triggers it's easy to miss internal triggers. A \d+ tablename will not show anything out of the ordinary for that situation since we don't show internal triggers. But foreign key checks won't work. So, how about displaying disabled internal triggers in psql? Hi had exactly the same concerns this morning while starting to look at the ENABLE/DISABLE constraint patch. However, i wouldn't display them as triggers, but maybe more generally as disabled constraints or such. Well, that will lead the user in the wrong direction, won't it? They haven't disabled the constraint but the trigger. Especially as we already have NOT VALID and might grow DISABLED for constraint themselves... Hi, The attached patch [1] enable PSQL to list internal disabled triggers in \d only in versions = 9.0. [1] psql-display-all-triggers-v1.patch http://postgresql.1045698.n5.nabble.com/file/n5775954/psql-display-all-triggers-v1.patch Hi all, I'm just send a new WIP patch rebased from master. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 96322ca..f457e21 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -2079,7 +2079,7 @@ describeOneTableDetails(const char *schemaname, (pset.sversion = 9 ? , true : ), oid); if (pset.sversion = 9) - appendPQExpBufferStr(buf, NOT t.tgisinternal); + appendPQExpBuffer(buf, (NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D'))); else if (pset.sversion = 80300) appendPQExpBufferStr(buf, t.tgconstraint = 0); else -- 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] Store Extension Options
On Thu, Nov 21, 2013 at 11:06 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Nov 20, 2013 at 9:35 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: So, with this patch we can do that: ALTER TABLE foo SET (ext.somext.do_replicate=true); When 'ext' is the fixed prefix, 'somext' is the extension name, 'do_replicate' is the extension option and 'true' is the value. This doesn't seem like a particular good choice of syntax; What's your syntax suggestion? I dunno, but I doubt that hardcoding ext as an abbreviation for extension is a good decision. I also doubt that any fixed prefix is a good decision. I use this form to simplify implementation and not change sql syntax, but we can discuss another way or syntax. and I also have my doubts about the usefulness of the feature. This feature can be used for replication solutions, but also can be used for any extension that need do some specific configuration about tables, attributes and/or indexes. So, create your own configuration table with a column of type regclass. This can be a solution, but with a config table we have some problems: a) no dependency tracking (pg_depend) b) no correct locking c) no relcache d) harder to do correctly for columns Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] logical changeset generation v6.7
On Thu, Nov 14, 2013 at 11:46 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-11-12 18:50:33 +0100, Andres Freund wrote: You've actually changed the meaning of this section (and not in a good way): be set at server start. varnamewal_level/ must be set -to literalarchive/ or literalhot_standby/ to allow -connections from standby servers. +to literalarchive/, literalhot_standby/ or literallogical/ +to allow connections from standby servers. I think that the previous text meant that you needed archive - or, if you want to allow connections, hot_standby. The new text loses that nuance. Yea, that's because it was lost on me in the first place... I think that's because the nuance isn't actually in the text - note that it is talking about max_wal_senders and talking about connections *from*, not *to* standby servers. I've reformulated the wal_level paragraph and used or higher in several places now. Ok, so here's a rebased version of this. I tried to fix all the issues you mentioned, and it's based on the split off IsSystemRelation() patch, I've sent yesterday (included here). Hello, I'm trying to apply the patches but show some warnings/errors: $ gunzip -c /home/fabrizio/Downloads/0002-wal_decoding-Add-wal_level-logical-and-log-data-requ.patch.gz | git apply - warning: src/backend/access/transam/xlog.c has type 100755, expected 100644 $ gunzip -c /home/fabrizio/Downloads/0005-wal_decoding-Introduce-wal-decoding-via-catalog-time.patch.gz | git apply - warning: src/backend/access/transam/xlog.c has type 100755, expected 100644 $ gunzip -c /home/fabrizio/Downloads/0006-wal_decoding-Implement-VACUUM-FULL-CLUSTER-support-v.patch.gz | git apply - warning: src/backend/access/transam/xlog.c has type 100755, expected 100644 $ gunzip -c /home/fabrizio/Downloads/0007-wal_decoding-Only-peg-the-xmin-horizon-for-catalog-t.patch.gz | git apply - warning: src/backend/access/transam/xlog.c has type 100755, expected 100644 $ gunzip -c /home/fabrizio/Downloads/0011-wal_decoding-pg_recvlogical-Introduce-pg_receivexlog.patch.gz | git apply - error: patch failed: src/bin/pg_basebackup/streamutil.c:210 error: src/bin/pg_basebackup/streamutil.c: patch does not apply The others are applied correctly. The permission warning must be fixed and 0011 bust be rebased. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Time-Delayed Standbys
On Fri, Nov 29, 2013 at 5:49 AM, KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp wrote: Hi Royes, I'm sorry for my late review... No problem... I feel potential of your patch in PG replication function, and it might be something useful for all people. I check your patch and have some comment for improvement. I haven't executed detail of unexpected sutuation yet. But I think that under following problem2 is important functionality problem. So I ask you to solve the problem in first. * Regress test No problem. * Problem1 Your patch does not code recovery.conf.sample about recovery_time_delay. Please add it. Fixed. * Problem2 When I set time-delayed standby and start standby server, I cannot access stanby server by psql. It is because PG is in first starting recovery which cannot access by psql. I think that time-delayed standby is only delayed recovery position, it must not affect other functionality. I didn't test recoevery in master server with recovery_time_delay. If you have detail test result of these cases, please send me. Well, I could not reproduce the problem that you described. I run the following test: 1) Clusters - build master - build slave and attach to the master using SR and config recovery_time_delay to 1min. 2) Stop de Slave 3) Run some transactions on the master using pgbench to generate a lot of archives 4) Start the slave and connect to it using psql and in another session I can see all archive recovery log My first easy review of your patch is that all. Thanks. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello diff --git a/doc/src/sgml/recovery-config.sgml b/doc/src/sgml/recovery-config.sgml index c0c543e..641c9c6 100644 --- a/doc/src/sgml/recovery-config.sgml +++ b/doc/src/sgml/recovery-config.sgml @@ -135,6 +135,27 @@ restore_command = 'copy C:\\server\\archivedir\\%f %p' # Windows /listitem /varlistentry + varlistentry id=recovery-time-delay xreflabel=recovery_time_delay + termvarnamerecovery_time_delay/varname (typeinteger/type)/term + indexterm +primaryvarnamerecovery_time_delay/ recovery parameter/primary + /indexterm + listitem + para +Specifies the amount of time (in milliseconds, if no unit is specified) +which recovery of transaction commits should lag the master. This +parameter allows creation of a time-delayed standby. For example, if +you set this parameter to literal5min/literal, the standby will +replay each transaction commit only when the system time on the standby +is at least five minutes past the commit time reported by the master. + /para + para +Note that if the master and standby system clocks are not synchronized, +this might lead to unexpected results. + /para + /listitem + /varlistentry + /variablelist /sect1 diff --git a/src/backend/access/transam/recovery.conf.sample b/src/backend/access/transam/recovery.conf.sample index 5acfa57..97cc7af 100644 --- a/src/backend/access/transam/recovery.conf.sample +++ b/src/backend/access/transam/recovery.conf.sample @@ -123,6 +123,17 @@ # #trigger_file = '' # +# recovery_time_delay +# +# By default, a standby server keeps restoring XLOG records from the +# primary as soon as possible. If you want to delay the replay of +# commited transactions from the master, specify a recovery time delay. +# For example, if you set this parameter to 5min, the standby will replay +# each transaction commit only whe the system time on the standby is least +# five minutes past the commit time reported by the master. +# +#recovery_time_delay = 0 +# #--- # HOT STANDBY PARAMETERS #--- diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index de19d22..714b1bd 100755 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -218,6 +218,8 @@ static bool recoveryPauseAtTarget = true; static TransactionId recoveryTargetXid; static TimestampTz recoveryTargetTime; static char *recoveryTargetName; +static int recovery_time_delay = 0; +static TimestampTz recoveryDelayUntilTime; /* options taken from recovery.conf for XLOG streaming */ static bool StandbyModeRequested = false; @@ -730,6 +732,7 @@ static void readRecoveryCommandFile(void); static void exitArchiveRecovery(TimeLineID endTLI, XLogSegNo endLogSegNo); static bool recoveryStopsHere(XLogRecord *record, bool *includeThis); static void recoveryPausesHere(void); +static void recoveryDelay(void); static void SetLatestXTime(TimestampTz xtime); static void SetCurrentChunkStartTime(TimestampTz
Re: [HACKERS] Time-Delayed Standbys
On Tue, Dec 3, 2013 at 2:33 PM, Christian Kruse christ...@2ndquadrant.comwrote: Hi Fabrizio, looks good to me. I did some testing on 9.2.4, 9.2.5 and HEAD. It applies and compiles w/o errors or warnings. I set up a master and two hot standbys replicating from the master, one with 5 minutes delay and one without delay. After that I created a new database and generated some test data: CREATE TABLE test (val INTEGER); INSERT INTO test (val) (SELECT * FROM generate_series(0, 100)); The non-delayed standby nearly instantly had the data replicated, the delayed standby was replicated after exactly 5 minutes. I did not notice any problems, errors or warnings. Thanks for your review Christian... Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Time-Delayed Standbys
On Thu, Dec 5, 2013 at 7:57 AM, Simon Riggs si...@2ndquadrant.com wrote: On 5 December 2013 08:51, Magnus Hagander mag...@hagander.net wrote: Not recalling the older thread, but it seems the breaks on clock drift, I think we can fairly easily make that situation good enough. Just have IDENTIFY_SYSTEM return the current timestamp on the master, and refuse to start if the time difference is too great. Yes, that doesn't catch the case when the machines are in perfect sync when they start up and drift *later*, but it will catch the most common cases I bet. But I think that's good enough that we can accept the feature, given that *most* people will have ntp, and that it's a very useful feature for those people. But we could help people who run into it because of a simple config error.. Or maybe the suggested patch already does this, in which case ignore that part :) I think the very nature of *this* feature is that it doesnt *require* the clocks to be exactly in sync, even though that is the basis for measurement. The setting of this parameter for sane usage would be minimum 5 mins, but more likely 30 mins, 1 hour or more. In that case, a few seconds drift either way makes no real difference to this feature. So IMHO, without prejudice to other features that may be more critically reliant on time synchronisation, we are OK to proceed with this specific feature. Hi all, I saw the comments of all of you. I'm a few busy with some customers issues (has been a crazy week), but I'll reply and/or fix your suggestions later. Thanks for all review and sorry to delay in reply. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] dblink performance regression
On Fri, Dec 6, 2013 at 1:05 AM, Joe Conway m...@joeconway.com wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 12/05/2013 06:53 PM, Tom Lane wrote:
I seem to remember that at some point we realized that the encoding
ID assignments are part of libpq's ABI and so can't practically be
changed ever, so the above may be moot. Even so, I think it's a
bad idea to be depending on pg_encoding_to_char() here, given the
ambiguity in what it references. It would be unsurprising to get
build-time or run-time failures on pickier platforms, as a
consequence of that ambiguity. So I'd still recommend comparing
integer IDs as above, rather than this.
Great feedback as always -- thanks! Will make that change.
Hi Joe, how are you?
Well, when Tom sent this email I was reviewing your patch and the main
suggestion is about use of 'pg_encoding_to_char' too... ;-)
The attached patch with my review!
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Timbira: http://www.timbira.com.br
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
diff --git a/contrib/dblink/dblink.c b/contrib/dblink/dblink.c
index d68b12a..c358734 100644
--- a/contrib/dblink/dblink.c
+++ b/contrib/dblink/dblink.c
@@ -99,6 +99,7 @@ static char *generate_relation_name(Relation rel);
static void dblink_connstr_check(const char *connstr);
static void dblink_security_check(PGconn *conn, remoteConn *rconn);
static void dblink_res_error(const char *conname, PGresult *res, const char
*dblink_context_msg, bool fail);
+static void dblink_set_client_encoding(PGconn *conn);
static char *get_connect_string(const char *servername);
static char *escape_param_str(const char *from);
static void validate_pkattnums(Relation rel,
@@ -190,7 +191,7 @@ typedef struct remoteConnHashEnt
errdetail(%s,
msg))); \
} \
dblink_security_check(conn, rconn); \
- PQsetClientEncoding(conn,
GetDatabaseEncodingName()); \
+ dblink_set_client_encoding(conn); \
freeconn = true; \
} \
} while (0)
@@ -270,7 +271,7 @@ dblink_connect(PG_FUNCTION_ARGS)
dblink_security_check(conn, rconn);
/* attempt to set client encoding to match server encoding */
- PQsetClientEncoding(conn, GetDatabaseEncodingName());
+ dblink_set_client_encoding(conn);
if (connname)
{
@@ -2328,6 +2329,13 @@ dblink_res_error(const char *conname, PGresult *res,
const char *dblink_context_
dblink_context_conname,
dblink_context_msg)));
}
+static void
+dblink_set_client_encoding(PGconn *conn)
+{
+ if (PQclientEncoding(conn) != GetDatabaseEncoding())
+ PQsetClientEncoding(conn, GetDatabaseEncodingName());
+}
+
/*
* Obtain connection string for a foreign server
*/
--
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] Time-Delayed Standbys
On Tue, Dec 3, 2013 at 5:33 PM, Simon Riggs si...@2ndquadrant.com wrote: - compute recoveryUntilDelayTime in XLOG_XACT_COMMIT and XLOG_XACT_COMMIT_COMPACT checks Why just those? Why not aborts and restore points also? I think make no sense execute the delay after aborts and/or restore points, because it not change data in a standby server. - don't care about clockdrift because it's an admin problem. Few minor points on things * The code with comment Clear any previous recovery delay time is in wrong place, move down or remove completely. Setting the delay to zero doesn't prevent calling recoveryDelay(), so that logic looks wrong anyway. Fixed. * The loop exit in recoveryDelay() is inelegant, should break if = 0 Fixed. * There's a spelling mistake in sample Fixed. * The patch has whitespace in one place Fixed. and one important point... * The delay loop happens AFTER we check for a pause. Which means if the user notices a problem on a commit, then hits pause button on the standby, the pause will have no effect and the next commit will be applied anyway. Maybe just one commit, but its an off by one error that removes the benefit of the patch. So I think we need to test this again after we finish delaying if (xlogctl-recoveryPause) recoveryPausesHere(); Fixed. We need to explain in the docs that this is intended only for use in a live streaming deployment. It will have little or no meaning in a PITR. Fixed. I think recovery_time_delay should be called something_apply_delay to highlight the point that it is the apply of records that is delayed, not the receipt. And hence the need to document that sync rep is NOT slowed down by setting this value. Fixed. And to make the name consistent with other parameters, I suggest min_standby_apply_delay I agree. Fixed! We also need to document caveats about the patch, in that it only delays on timestamped WAL records and other records may be applied sooner than the delay in some circumstances, so it is not a way to avoid all cancellations. We also need to document the behaviour of the patch is to apply all data received as quickly as possible once triggered, so the specified delay does not slow down promoting the server to a master. That might also be seen as a negative behaviour, since promoting the master effectively sets recovery_time_delay to zero. I will handle the additional documentation, if you can update the patch with the main review comments. Thanks. Thanks, your help is welcome. Att, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello diff --git a/doc/src/sgml/recovery-config.sgml b/doc/src/sgml/recovery-config.sgml index 9d80256..12aa917 100644 --- a/doc/src/sgml/recovery-config.sgml +++ b/doc/src/sgml/recovery-config.sgml @@ -142,6 +142,31 @@ restore_command = 'copy C:\\server\\archivedir\\%f %p' # Windows /listitem /varlistentry + varlistentry id=min-standby-apply-delay xreflabel=min_standby_apply_delay + termvarnamemin_standby_apply_delay/varname (typeinteger/type)/term + indexterm +primaryvarnamemin_standby_apply_delay/ recovery parameter/primary + /indexterm + listitem + para +Specifies the amount of time (in milliseconds, if no unit is specified) +which recovery of transaction commits should lag the master. This +parameter allows creation of a time-delayed standby. For example, if +you set this parameter to literal5min/literal, the standby will +replay each transaction commit only when the system time on the standby +is at least five minutes past the commit time reported by the master. + /para + para +Note that if the master and standby system clocks are not synchronized, +this might lead to unexpected results. + /para + para +This parameter works only for streaming replication deployments. Synchronous +replicas and PITR has not affected. + /para + /listitem + /varlistentry + /variablelist /sect1 diff --git a/src/backend/access/transam/recovery.conf.sample b/src/backend/access/transam/recovery.conf.sample index 5acfa57..e8617db 100644 --- a/src/backend/access/transam/recovery.conf.sample +++ b/src/backend/access/transam/recovery.conf.sample @@ -123,6 +123,17 @@ # #trigger_file = '' # +# min_standby_apply_delay +# +# By default, a standby server keeps restoring XLOG records from the +# primary as soon as possible. If you want to delay the replay of +# commited transactions from the master, specify a recovery time delay. +# For example, if you set this parameter to 5min, the standby will replay +# each transaction commit only when the system time on the standby is least
Re: [HACKERS] dblink performance regression
On Fri, Dec 6, 2013 at 2:50 AM, Joe Conway m...@joeconway.com wrote: On 12/05/2013 07:16 PM, Fabrízio de Royes Mello wrote: Hi Joe, how are you? Hi Fabrizio -- great to hear from you! I'm well. :-) Well, when Tom sent this email I was reviewing your patch and the main suggestion is about use of 'pg_encoding_to_char' too... ;-) The attached patch with my review! Awesome, thanks for the review! You're welcome! Greetings, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Time-Delayed Standbys
On Fri, Dec 6, 2013 at 1:36 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Dec 5, 2013 at 11:07 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: On Tue, Dec 3, 2013 at 5:33 PM, Simon Riggs si...@2ndquadrant.com wrote: - compute recoveryUntilDelayTime in XLOG_XACT_COMMIT and XLOG_XACT_COMMIT_COMPACT checks Why just those? Why not aborts and restore points also? I think make no sense execute the delay after aborts and/or restore points, because it not change data in a standby server. I see no reason to pause for aborts. Aside from the fact that it wouldn't be reliable in corner cases, as Fabrízio says, there's no user-visible effect, just as there's no user-visible effect from replaying a transaction up until just prior to the point where it commits (which we also do). Waiting for restore points seems like it potentially makes sense. If the standby is delayed by an hour, and you create a restore point and wait 55 minutes, you might expect that that you can still kill the standby and recover it to that restore point. Makes sense. Fixed. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello diff --git a/doc/src/sgml/recovery-config.sgml b/doc/src/sgml/recovery-config.sgml index 9d80256..12aa917 100644 --- a/doc/src/sgml/recovery-config.sgml +++ b/doc/src/sgml/recovery-config.sgml @@ -142,6 +142,31 @@ restore_command = 'copy C:\\server\\archivedir\\%f %p' # Windows /listitem /varlistentry + varlistentry id=min-standby-apply-delay xreflabel=min_standby_apply_delay + termvarnamemin_standby_apply_delay/varname (typeinteger/type)/term + indexterm +primaryvarnamemin_standby_apply_delay/ recovery parameter/primary + /indexterm + listitem + para +Specifies the amount of time (in milliseconds, if no unit is specified) +which recovery of transaction commits should lag the master. This +parameter allows creation of a time-delayed standby. For example, if +you set this parameter to literal5min/literal, the standby will +replay each transaction commit only when the system time on the standby +is at least five minutes past the commit time reported by the master. + /para + para +Note that if the master and standby system clocks are not synchronized, +this might lead to unexpected results. + /para + para +This parameter works only for streaming replication deployments. Synchronous +replicas and PITR has not affected. + /para + /listitem + /varlistentry + /variablelist /sect1 diff --git a/src/backend/access/transam/recovery.conf.sample b/src/backend/access/transam/recovery.conf.sample index 5acfa57..e8617db 100644 --- a/src/backend/access/transam/recovery.conf.sample +++ b/src/backend/access/transam/recovery.conf.sample @@ -123,6 +123,17 @@ # #trigger_file = '' # +# min_standby_apply_delay +# +# By default, a standby server keeps restoring XLOG records from the +# primary as soon as possible. If you want to delay the replay of +# commited transactions from the master, specify a recovery time delay. +# For example, if you set this parameter to 5min, the standby will replay +# each transaction commit only when the system time on the standby is least +# five minutes past the commit time reported by the master. +# +#min_standby_apply_delay = 0 +# #--- # HOT STANDBY PARAMETERS #--- diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index b68230d..7ca2f9b 100755 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -218,6 +218,8 @@ static bool recoveryPauseAtTarget = true; static TransactionId recoveryTargetXid; static TimestampTz recoveryTargetTime; static char *recoveryTargetName; +static int min_standby_apply_delay = 0; +static TimestampTz recoveryDelayUntilTime; /* options taken from recovery.conf for XLOG streaming */ static bool StandbyModeRequested = false; @@ -730,6 +732,7 @@ static void readRecoveryCommandFile(void); static void exitArchiveRecovery(TimeLineID endTLI, XLogSegNo endLogSegNo); static bool recoveryStopsHere(XLogRecord *record, bool *includeThis); static void recoveryPausesHere(void); +static void recoveryDelay(void); static void SetLatestXTime(TimestampTz xtime); static void SetCurrentChunkStartTime(TimestampTz xtime); static void CheckRequiredParameterValues(void); @@ -5474,6 +5477,19 @@ readRecoveryCommandFile(void) (errmsg_internal(trigger_file = '%s', TriggerFile))); } + else if (strcmp(item-name, min_standby_apply_delay) == 0
Re: [HACKERS] dblink performance regression
On Sat, Dec 7, 2013 at 11:07 PM, Joe Conway m...@joeconway.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/05/2013 07:05 PM, Joe Conway wrote: On 12/05/2013 06:53 PM, Tom Lane wrote: I seem to remember that at some point we realized that the encoding ID assignments are part of libpq's ABI and so can't practically be changed ever, so the above may be moot. Even so, I think it's a bad idea to be depending on pg_encoding_to_char() here, given the ambiguity in what it references. It would be unsurprising to get build-time or run-time failures on pickier platforms, as a consequence of that ambiguity. So I'd still recommend comparing integer IDs as above, rather than this. Great feedback as always -- thanks! Will make that change. Committed to all active branches. IMHO is more elegant create a procedure to encapsulate the code to avoid redundancy. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] dblink performance regression
On Sat, Dec 7, 2013 at 11:20 PM, Michael Paquier michael.paqu...@gmail.com wrote: IMHO is more elegant create a procedure to encapsulate the code to avoid redundancy. Yep, perhaps something like PQsetClientEncodingIfDifferent or similar would make sense. Well I think at this first moment we can just create a procedure inside the dblink contrib and not touch in libpq. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] dblink performance regression
On Sat, Dec 7, 2013 at 11:41 PM, Fabrízio de Royes Mello
fabriziome...@gmail.com wrote:
On Sat, Dec 7, 2013 at 11:20 PM, Michael Paquier
michael.paqu...@gmail.com wrote:
IMHO is more elegant create a procedure to encapsulate the code to
avoid
redundancy.
Yep, perhaps something like PQsetClientEncodingIfDifferent or similar
would make sense.
Well I think at this first moment we can just create a procedure inside
the dblink contrib and not touch in libpq.
Something like the attached.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Timbira: http://www.timbira.com.br
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
diff --git a/contrib/dblink/dblink.c b/contrib/dblink/dblink.c
index a91a547..1feee22 100644
--- a/contrib/dblink/dblink.c
+++ b/contrib/dblink/dblink.c
@@ -112,6 +112,7 @@ static Relation get_rel_from_relname(text *relname_text, LOCKMODE lockmode, AclM
static char *generate_relation_name(Relation rel);
static void dblink_connstr_check(const char *connstr);
static void dblink_security_check(PGconn *conn, remoteConn *rconn);
+static void dblink_set_client_encoding(PGconn *conn);
static void dblink_res_error(const char *conname, PGresult *res, const char *dblink_context_msg, bool fail);
static char *get_connect_string(const char *servername);
static char *escape_param_str(const char *from);
@@ -209,8 +210,7 @@ typedef struct remoteConnHashEnt
errdetail_internal(%s, msg))); \
} \
dblink_security_check(conn, rconn); \
-if (PQclientEncoding(conn) != GetDatabaseEncoding()) \
- PQsetClientEncoding(conn, GetDatabaseEncodingName()); \
+dblink_set_client_encoding(conn); \
freeconn = true; \
} \
} while (0)
@@ -290,8 +290,7 @@ dblink_connect(PG_FUNCTION_ARGS)
dblink_security_check(conn, rconn);
/* attempt to set client encoding to match server encoding, if needed */
- if (PQclientEncoding(conn) != GetDatabaseEncoding())
- PQsetClientEncoding(conn, GetDatabaseEncodingName());
+ dblink_set_client_encoding(conn);
if (connname)
{
@@ -2622,6 +2621,13 @@ dblink_security_check(PGconn *conn, remoteConn *rconn)
}
}
+static void
+dblink_set_client_encoding(PGconn *conn)
+{
+ if (PQclientEncoding(conn) != GetDatabaseEncoding())
+ PQsetClientEncoding(conn, GetDatabaseEncodingName());
+}
+
/*
* For non-superusers, insist that the connstr specify a password. This
* prevents a password from being picked up from .pgpass, a service file,
--
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] Time-Delayed Standbys
On Wed, Dec 11, 2013 at 6:27 AM, Simon Riggs si...@2ndquadrant.com wrote: On 11 December 2013 06:36, KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp wrote: I think this feature will be used in a lot of scenarios in which PITR is currently used. We have to judge which is better, we get something potential or to protect stupid. And we had better to wait author's comment... I'd say just document that it wouldn't make sense to use it for PITR. There may be some use case we can't see yet, so specifically prohibiting a use case that is not dangerous seems too much at this point. I will no doubt be reminded of these words in the future... Hi all, I tend to agree with Simon, but I confess that I don't liked to delay a server with standby_mode = 'off'. The main goal of this patch is delay the Streaming Replication, so if the slave server isn't a hot-standby I think makes no sense to delay it. Mitsumasa suggested to add StandbyModeRequested in conditional branch to skip this situation. I agree with him! And I'll change 'recoveryDelay' (functions, variables) to 'standbyDelay'. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Time-Delayed Standbys
On Wed, Dec 11, 2013 at 7:47 PM, Andres Freund and...@2ndquadrant.com wrote: I don't think that position has any merit, sorry: Think about the way this stuff gets setup. The user creates a new basebackup (pg_basebackup, manual pg_start/stop_backup, shutdown primary). Then he creates a recovery conf by either starting from scratch, using --write-recovery-conf or by copying recovery.conf.sample. In none of these cases delay will be configured. Ok. So, with that in mind, the only way it could have been configured is by the user *explicitly* writing it into recovery.conf. And now you want to to react to this explicit step by just *silently* ignoring the setting based on some random criteria (arguments have been made about hot_standby=on/off, standby_mode=on/off which aren't directly related). Why on earth would that by a usability improvement? Also, you seem to assume there's no point in configuring it for any of hot_standby=off, standby_mode=off, recovery_target=*. Why? There's usecases for all of them: * hot_standby=off: Makes delay useable with wal_level=archive (and thus a lower WAL volume) * standby_mode=off: Configurations that use tools like pg_standby and similar simply don't need standby_mode=on. If you want to trigger failover from within the restore_command you *cannot* set it. * recovery_target_*: It can still make sense if you use pause_at_recovery_target. In which scenarios does your restriction actually improve anything? Given your arguments I'm forced to review my understanding of the problem. You are absolutely right in your assertions. I was not seeing the scenario on this perspective. Anyway we need to improve docs, any suggestions? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Time-Delayed Standbys
On Thu, Dec 12, 2013 at 3:39 PM, Simon Riggs si...@2ndquadrant.com wrote: On 12 December 2013 15:19, Simon Riggs si...@2ndquadrant.com wrote: Don't panic guys! I meant UTC offset only. And yes, it may not be needed, will check. Checked, all non-UTC TZ offsets work without further effort here. Thanks! -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Time-Delayed Standbys
On Thu, Dec 12, 2013 at 3:42 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: On Thu, Dec 12, 2013 at 3:39 PM, Simon Riggs si...@2ndquadrant.com wrote: On 12 December 2013 15:19, Simon Riggs si...@2ndquadrant.com wrote: Don't panic guys! I meant UTC offset only. And yes, it may not be needed, will check. Checked, all non-UTC TZ offsets work without further effort here. Thanks! Reviewing the committed patch I noted that the CheckForStandbyTrigger() after the delay was removed. If we promote the standby during the delay and don't check the trigger immediately after the delay, then we will replay undesired WALs records. The attached patch add this check. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index a76aef3..fbc2d2f 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -6835,6 +6835,14 @@ StartupXLOG(void) recoveryApplyDelay(); /* + * Check for standby trigger to prevent the + * replay of undesired WAL records if the + * slave was promoted during the delay. + */ + if (CheckForStandbyTrigger()) + break; + + /* * We test for paused recovery again here. If * user sets delayed apply, it may be because * they expect to pause recovery in case of -- 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: make_timestamp function
On Thu, Dec 12, 2013 at 3:11 PM, Pavel Stehule pavel.steh...@gmail.comwrote: Hello this patch try to complete a set of functions make_date and make_timestamp. Could we have the 'make_timestamptz' function too? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] patch: make_timestamp function
On Fri, Dec 13, 2013 at 7:09 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I though about it. Then there are two questions a) Could we have a make_timetz function? b) What type we use for timezone? I just think in a function that returns the timestamp with timezone based on the current 'timezone' setting. fabrizio=# show timezone; TimeZone - Brazil/East (1 row) fabrizio=# select '2013-12-13 11:29:45.786937'::timestamptz; timestamptz --- 2013-12-13 11:29:45.786937-02 (1 row) fabrizio=# set timezone to 'UTC'; SET fabrizio=# select '2013-12-13 11:29:45.786937'::timestamptz; timestamptz --- 2013-12-13 11:29:45.786937+00 (1 row) Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Time-Delayed Standbys
On Fri, Dec 13, 2013 at 11:44 AM, Simon Riggs si...@2ndquadrant.com wrote: On 13 December 2013 13:22, Andres Freund and...@2ndquadrant.com wrote: On 2013-12-13 13:09:13 +, Simon Riggs wrote: On 13 December 2013 11:58, Andres Freund and...@2ndquadrant.com wrote: On 2013-12-13 11:56:47 +, Simon Riggs wrote: On 12 December 2013 21:58, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: Reviewing the committed patch I noted that the CheckForStandbyTrigger() after the delay was removed. If we promote the standby during the delay and don't check the trigger immediately after the delay, then we will replay undesired WALs records. The attached patch add this check. I removed it because it was after the pause. I'll replace it, but before the pause. Doesn't after the pause make more sense? If somebody promoted while we were waiting, we want to recognize that before rolling forward? The wait can take a long while after all? That would change the way pause currently works, which is OOS for that patch. But this feature isn't pause itself - it's imo something independent. Note that we currently a) check pause again after recoveryApplyDelay(), b) do check for promotion if the sleep in recoveryApplyDelay() is interrupted. So not checking after the final sleep seems confusing. I'm proposing the attached patch. This patch implements a consistent view of recovery pause, which is that when paused, we don't check for promotion, during or immediately after. That is user noticeable behaviour and shouldn't be changed without thought and discussion on a separate thread with a clear descriptive title. (I might argue in favour of it myself, I'm not yet decided). In my previous message [1] I attach a patch equal to your ;-) Regards, [1] http://www.postgresql.org/message-id/CAFcNs+qD0AJ=qzhsHD9+v_Mhz0RTBJ=cJPCT_T=ut_jvvnc...@mail.gmail.com -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] patch: make_timestamp function
On Fri, Dec 13, 2013 at 3:53 PM, Martijn van Oosterhout klep...@svana.org wrote: On Thu, Dec 12, 2013 at 08:50:26PM -0200, Fabrízio de Royes Mello wrote: On Thu, Dec 12, 2013 at 3:11 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello this patch try to complete a set of functions make_date and make_timestamp. Could we have the 'make_timestamptz' function too? Wouldn't this just be: SELECT make_timestamp(...) at time zone 'foo'; (assuming make_timestamp actually returns a timestamp and not a timestamptz). or do you mean something else? Your example will convert the timestamp into time zone defined by 'at time zone...'. I think the goal of the make_date/time/timestamp function series is build a date/time/timestamp from scratch, so the use of 'make_timestamptz' is to build a specific timestamp with timezone and don't convert it. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] patch: make_timestamp function
On Fri, Dec 13, 2013 at 5:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: =?ISO-8859-1?Q?Fabr=EDzio_de_Royes_Mello?= fabriziome...@gmail.com writes: I think the goal of the make_date/time/timestamp function series is build a date/time/timestamp from scratch, so the use of 'make_timestamptz' is to build a specific timestamp with timezone and don't convert it. Yeah; we don't really want to incur an extra timezone rotation just to get to a timestamptz. However, it's not clear to me if make_timestamptz() needs to have an explicit zone parameter or not. It could just assume that you meant the active timezone. +1. And if you want a different timezone you can just set the 'timezone' GUC. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] trailing comment ghost-timing
On Tue, Dec 24, 2013 at 5:53 AM, Martijn van Oosterhout klep...@svana.org wrote: On Tue, Dec 24, 2013 at 03:40:58AM +0100, Andreas Karlsson wrote: On 12/24/2013 03:17 AM, David Johnston wrote: It is not sent to the server as a trailing comment. The following file is sent to the server like this. File: /**/; /**/ Commands: PQexec(..., /**/;); PQexec(..., /**/); If this has to be fixed it should be in the client. I think people would complain if we broke the API by starting to throw an exception on PQexec with a string containing no actual query. (Untested). Isn't this just a case of psql not printing out a timing if the server responds with PGRES_EMPTY_QUERY? Works... look to the attached patch! Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c index bbdafab..7320f31 100644 --- a/src/bin/psql/common.c +++ b/src/bin/psql/common.c @@ -1023,12 +1023,12 @@ SendQuery(const char *query) } } - PQclear(results); - /* Possible microtiming output */ - if (pset.timing) + if (pset.timing PQresultStatus(results) != PGRES_EMPTY_QUERY) printf(_(Time: %.3f ms\n), elapsed_msec); + PQclear(results); + /* check for events that may occur during query execution */ if (pset.encoding != PQclientEncoding(pset.db) -- 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] Store Extension Options
On Tue, Dec 31, 2013 at 9:08 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello I am looking on this patch ALTER TABLE foo SET (ext.somext.do_replicate=true); Why is there fixed prefix ext ? This feature is similar to attaching setting to function CREATE OR REPLACE FUNCTION ... SET var = ...; We can use someprefix.someguc without problems there. Hi, We use the prefix ext (aka namespace) to distinguish these options which are related to extensions. Have you seen the previous thread [1] ? Regards, [1] http://www.postgresql.org/message-id/CAFcNs+rqCq1H5eXW-cvdti6T-xo2STEkhjREx=odmakk5ti...@mail.gmail.com -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] [PATCH] Store Extension Options
On Tue, Dec 31, 2013 at 9:38 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2013/12/31 Fabrízio de Royes Mello fabriziome...@gmail.com On Tue, Dec 31, 2013 at 9:08 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello I am looking on this patch ALTER TABLE foo SET (ext.somext.do_replicate=true); Why is there fixed prefix ext ? This feature is similar to attaching setting to function CREATE OR REPLACE FUNCTION ... SET var = ...; We can use someprefix.someguc without problems there. Hi, We use the prefix ext (aka namespace) to distinguish these options which are related to extensions. Have you seen the previous thread [1] ? yes, but I don't understand why it is necessary? I use a analogy with custom GUC - and there we don't use similar prefix. Only any prefix is required - and it can contain a dot. We use the namespace ext to the internal code (src/backend/access/common/reloptions.c) skip some validations and store the custom GUC. Do you think we don't need to use the ext namespace? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] [PATCH] Store Extension Options
On Tue, Dec 31, 2013 at 10:37 AM, Pavel Stehule pavel.steh...@gmail.com
wrote:
2013/12/31 Fabrízio de Royes Mello fabriziome...@gmail.com
On Tue, Dec 31, 2013 at 9:38 AM, Pavel Stehule pavel.steh...@gmail.com
wrote:
2013/12/31 Fabrízio de Royes Mello fabriziome...@gmail.com
On Tue, Dec 31, 2013 at 9:08 AM, Pavel Stehule
pavel.steh...@gmail.com wrote:
Hello
I am looking on this patch
ALTER TABLE foo SET (ext.somext.do_replicate=true);
Why is there fixed prefix ext ?
This feature is similar to attaching setting to function
CREATE OR REPLACE FUNCTION ... SET var = ...;
We can use someprefix.someguc without problems there.
Hi,
We use the prefix ext (aka namespace) to distinguish these options
which are related to extensions.
Have you seen the previous thread [1] ?
yes, but I don't understand why it is necessary? I use a analogy with
custom GUC - and there we don't use similar prefix. Only any prefix is
required - and it can contain a dot.
We use the namespace ext to the internal code
(src/backend/access/common/reloptions.c) skip some validations and store
the custom GUC.
Do you think we don't need to use the ext namespace?
yes - there be same mechanism as we use for GUC
If we going to that way then we can expand the use of this patch to store
custom GUCs to functions also, and we can wrote a function (like
current_setting) to get specific GUC values, like:
ALTER TABLE foo SET (myextension.option=on);
SELECT current_setting('foo'::regclass, 'myextension.option');
Comments?
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Timbira: http://www.timbira.com.br
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] [PATCH] Store Extension Options
On Thu, Jan 2, 2014 at 7:19 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-12-31 13:37:59 +0100, Pavel Stehule wrote: We use the namespace ext to the internal code (src/backend/access/common/reloptions.c) skip some validations and store the custom GUC. Do you think we don't need to use the ext namespace? yes - there be same mechanism as we use for GUC There is no existing mechanism to handle conflicts for GUCs. The difference is that for GUCs nearly no namespaced GUCs exist (plperl, plpgsql have some), but postgres defines at least autovacuum. and toast. namespaces for relation options. autovacuum. namespace ??? The HEAP_RELOPT_NAMESPACES (src/include/access/reloptions.h) constant define only toast and null as a valid relation option namespace. I missed something? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] [PATCH] Store Extension Options
On Mon, Jan 6, 2014 at 1:08 AM, Robert Haas robertmh...@gmail.com wrote: On Sun, Jan 5, 2014 at 3:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Jan 4, 2014 at 1:00 PM, Tom Lane t...@sss.pgh.pa.us wrote: I would suggest addressing Robert's concern about lack of error checking by refusing to allow a custom reloption to be set unless the relevant extension is loaded and checks it. Unlike the postgresql.conf problem, I don't see any very good use-case for allowing an unchecked ALTER TABLE to occur. How do you plan to resolve the associated dump/restore hazard? pg_dump creates extensions before tables, no? So what dump/restore hazard? Creating the extension doesn't guarantee that the shared library will always be loaded. If nothing else, think about partial restores. You are correct. pg_dump export reloptions using WITH clause of CREATE TABLE statement. I.e.: CREATE TABLE foo ( ) WITH (autovacuum_enabled=false, bdr.do_replicate=false); So if this statement checks for 'bdr' extension is loaded then in partial restore it can be fail. At this point we have two choices: 1) do not check if extension already is loaded 2) hack the pg_dump to produce an ALTER TABLE ... SET (...) instead of CREATE TABLE ... WITH (...) to set reloptions Comments? -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] commit fest manager?
On Wed, Jan 8, 2014 at 8:43 PM, Joshua D. Drake j...@commandprompt.com wrote: On 01/08/2014 02:38 PM, Josh Berkus wrote: On 01/08/2014 02:04 PM, Peter Eisentraut wrote: Anyone else? Or you'll have to deal with me again? I vote for Peter. +1 +1 -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] [PATCH] Store Extension Options
On Mon, Jan 6, 2014 at 1:50 AM, Tom Lane t...@sss.pgh.pa.us wrote: =?ISO-8859-1?Q?Fabr=EDzio_de_Royes_Mello?= fabriziome...@gmail.com writes: You are correct. pg_dump export reloptions using WITH clause of CREATE TABLE statement. I.e.: CREATE TABLE foo ( ) WITH (autovacuum_enabled=false, bdr.do_replicate=false); So if this statement checks for 'bdr' extension is loaded then in partial restore it can be fail. I see absolutely *nothing* wrong with failing that command if bdr is not installed. For an analogy, if this table includes a column of type bar defined by some extension baz, we are certainly going to fail the CREATE TABLE if baz isn't installed. Ok. Now, if bdr is installed but the validation doesn't happen unless bdr is loaded in some sense, then that is an implementation deficiency that I think we can insist be rectified before this feature is accepted. Check if extension is already installed is not enough for the first version of this feature? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS]
On Thu, Jan 9, 2014 at 2:50 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Jan 7, 2014 at 10:55 PM, Dilip kumar dilip.ku...@huawei.com wrote: Below attached patch is implementing following todo item.. machine-readable pg_controldata? http://www.postgresql.org/message-id/4b901d73.8030...@agliodbs.com Possible approaches: 1. Implement as backend function and provide a view to user. I think this would be useful. Hi, Joe Conway already created this feature [1] some time ago. I think this code can be reused to create an extension. Regards, [1] https://github.com/jconway/pg_controldata -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
