Re: [HACKERS] Measuring replay lag
Hi Just adding a couple of thoughts on this. On 03/14/2017 08:39 AM, Thomas Munro wrote: > Hi, > > Please see separate replies to Simon and Craig below. > > On Sun, Mar 5, 2017 at 8:38 PM, Simon Riggs <si...@2ndquadrant.com> wrote: >> On 1 March 2017 at 10:47, Thomas Munro <thomas.mu...@enterprisedb.com> wrote: >>> I do see why a new user trying this feature for the first time might >>> expect it to show a lag of 0 just as soon as sent LSN = >>> write/flush/apply LSN or something like that, but after some >>> reflection I suspect that it isn't useful information, and it would be >>> smoke and mirrors rather than real data. >> >> Perhaps I am misunderstanding the way it works. >> >> If the last time WAL was generated the lag was 14 secs, then nothing >> occurs for 2 hours aftwards AND all changes have been successfully >> applied then it should not continue to show 14 secs for the next 2 >> hours. >> >> IMHO the lag time should drop to zero in a reasonable time and stay at >> zero for those 2 hours because there is no current lag. >> >> If we want to show historical lag data, I'm supportive of the idea, >> but we must report an accurate current value when the system is busy >> and when the system is quiet. > > Ok, I thought about this for a bit and have a new idea that I hope > will be more acceptable. Here are the approaches considered: (...) > 2. Recognise when the last reported write/flush/apply LSN from the > standby == end of WAL on the sending server, and show lag times of > 00:00:00 in all three columns. I consider this entirely bogus: it's > not an actual measurement that was ever made, and on an active system > it would flip-flop between real measurements and the artificial > 00:00:00. I do not like this. I agree with this; while initially I was expecting to see 00:00:00, SQL NULL is definitely correct here. Anyone writing tools etc. which need to report an actual interval can convert this to 00:00:00 easily enough . (...) > 5. The new proposal: Show only true measured write/flush/apply data, > as in 1, but with a time limit. To avoid the scenario where we show > the same times during prolonged periods of idleness, clear the numbers > like in option 3 after a period of idleness. This way we avoid the > dreaded flickering/flip-flopping. A natural time to do that is when > wal_receiver_status_interval expires on idle systems and defaults to > 10 seconds. > > Done using approach 5 in the attached version. Do you think this is a > good compromise? No bogus numbers, only true measured > write/flush/apply times, but a time limit on 'stale' lag information. This makes sense to me. I'd also add that while on production servers it's likely there'll be enough activity to keep the columns updated, on a quiescent test/development systems seeing a stale value looks plain wrong (and will cause no end of questions from people asking why lag is still showing when their system isn't doing anything). I suggest the documentation of these columns needs to be extended to mention that they will be NULL if no lag was measured recently, and to explain the circumstances in which the numbers are cleared. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [DOCS] monitoring.sgml - clarify length of query text displayed in pg_stat_statements
Hi On 12/02/2016 11:01 PM, Robert Haas wrote: On Wed, Nov 30, 2016 at 8:45 PM, Ian Barwick <ian.barw...@2ndquadrant.com> wrote: Small doc patch to clarify how much of the query text is show in pg_stat_statements and a link to the relevant GUC. This patch improves the pg_stat_activity documentation, not the pg_stat_statements documentation, but it looks correct, so I have committed it. Many thanks! (Looks like I had a mental short-circuit between "query" and "statement" there). Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] monitoring.sgml - clarify length of query text displayed in pg_stat_statements
Hi Small doc patch to clarify how much of the query text is show in pg_stat_statements and a link to the relevant GUC. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml new file mode 100644 index 3de489e..02dab87 *** a/doc/src/sgml/monitoring.sgml --- b/doc/src/sgml/monitoring.sgml *** postgres 27093 0.0 0.0 30096 2752 *** 785,791 Text of this backend's most recent query. If state is active this field shows the currently executing query. In all other states, it shows the last query ! that was executed. --- 785,793 Text of this backend's most recent query. If state is active this field shows the currently executing query. In all other states, it shows the last query ! that was executed. By default the query text is truncated at 1024 ! characters; this value can be changed via the parameter ! . -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: tab completion for \l
Hi On 8/17/16 2:41 PM, Gerdan Santos wrote: > The following review has been posted through the commitfest application: > make installcheck-world: tested, passed > Implements feature: tested, passed > Spec compliant: tested, passed > Documentation:tested, passed > > I did some tests and found nothing special. The stated resource is > implemented correctly. > He passes all regression tests and enables the use of the new features > specified. > > The new status of this patch is: Ready for Committer Thanks for taking the time to review this! Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] psql: tab completion for \l
Hi
Evidently over the past 15 or so years I've never needed to type "\l ",
but when isolating a single database entry in a cluster with a lot more
databases than most I've encountered, was suprised to notice it didn't work.
Trivial patch attached, will add to next commitfest.
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
new file mode 100644
index a62ffe6..a87b483
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
*** psql_completion(const char *text, int st
*** 2994,2999
--- 2994,3001
COMPLETE_WITH_QUERY(Query_for_list_of_encodings);
else if (TailMatchesCS1("\\h") || TailMatchesCS1("\\help"))
COMPLETE_WITH_LIST(sql_commands);
+ else if (TailMatchesCS1("\\l*") && !TailMatchesCS1("\\lo_*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_databases);
else if (TailMatchesCS1("\\password"))
COMPLETE_WITH_QUERY(Query_for_list_of_roles);
else if (TailMatchesCS1("\\pset"))
--
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] Correction for replication slot creation error message in 9.6
On 05/04/16 10:24, Peter Eisentraut wrote: > On 03/30/2016 09:15 PM, Ian Barwick wrote: >> Currently pg_create_physical_replication_slot() may refer to >> the deprecated wal_level setting "archive": > > I have fixed this in the most direct way, since there was some disagreement > about rewording. Thanks! Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Correction for replication slot creation error message in 9.6
On 16/04/01 8:15, Michael Paquier wrote: > On Thu, Mar 31, 2016 at 11:18 PM, Alvaro Herrera > <alvhe...@2ndquadrant.com> wrote: >> Andres Freund wrote: >>> On 2016-03-31 10:15:21 +0900, Ian Barwick wrote: >> >>>> Patch changes the error message to: >>>> >>>> ERROR: replication slots can only be used if wal_level is "replica" or >>>> "logical" >>>> >>>> Explicitly naming the valid WAL levels matches the wording in the wal_level >>>> error hint used in a couple of places, i.e. >>> >>> The explicit naming makes it much more verbose to change anything around >>> wal level though, so consider me not a fan of spelling out all levels. >> >> I thought we had agreed that we weren't going to consider the wal_level >> values as a linear scale -- in other words, wordings such as "greater >> than FOO" are discouraged. That's always seemed a bit odd to me. > > Yes, that's what I thought as well. I don't remember if I saw that particular discussion, but same here. I suppose the alternative would be something like this: ERROR: replication slots cannot be used if wal_level is "minimal" (providing it remains the only "sub-replica" WAL level ;) ). Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Correction for replication slot creation error message in 9.6
Hi
Currently pg_create_physical_replication_slot() may refer to
the deprecated wal_level setting "archive":
postgres=# SHOW wal_level ;
wal_level
---
minimal
(1 row)
postgres=# SELECT pg_create_physical_replication_slot('some_slot');
ERROR: replication slots can only be used if wal_level is >= archive
Patch changes the error message to:
ERROR: replication slots can only be used if wal_level is "replica" or
"logical"
Explicitly naming the valid WAL levels matches the wording in the wal_level
error hint used in a couple of places, i.e.
"wal_level must be set to "replica" or "logical" at server start."
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
diff --git a/src/backend/replication/slot.c b/src/backend/replication/slot.c
new file mode 100644
index c13be75..82f6e65
*** a/src/backend/replication/slot.c
--- b/src/backend/replication/slot.c
*** CheckSlotRequirements(void)
*** 763,769
if (wal_level < WAL_LEVEL_REPLICA)
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
! errmsg("replication slots can only be used if wal_level >= archive")));
}
/*
--- 763,769
if (wal_level < WAL_LEVEL_REPLICA)
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
! errmsg("replication slots can only be used if wal_level is \"replica\" or \"logical\"")));
}
/*
--
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: RETURNING primary_key()
Hi
On 08/03/16 05:32, Igal @ Lucee.org wrote:
> THE ISSUE:
>
> In JDBC there is a flag called RETURN_GENERATED_KEYS --
> https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#RETURN_GENERATED_KEYS
>
(...)
> THE PROPOSAL:
>
> The proposal is to allow something like RETURNING primary_key() (it can be a
> keyword, not
> necessarily a function), e.g.
>
> INSERT INTO test VALUES ('PostgresQL') RETURNING primary_key();
FYI something similar has been proposed before:
http://www.postgresql.org/message-id/53953efb.8070...@2ndquadrant.com
The linked thread might provide more insights into the issues surrounding
this proposal.
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Description tweak for vacuumdb
Hi Like the docs say, vacuumdb is a "wrapper around the SQL command VACUUM" which I used to use in dim-and-distant pre-autovacuum days came for cronjobs, but until messing around with pg_upgrade recently I hadn't really had much use for it. Anyway, the docs also say "There is no effective difference between vacuuming and analyzing databases via this utility and via other methods for accessing the server", which IMHO seems a bit out-of-date as it now does two things which you can't do directly via e.g. psql: - generate statistics in stages (9.4) - parallel vacuum (9.5) Attached patches (for 9.4 and 9.5/HEAD) update the description to make clear that it now does a bit more than just execute a single command. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml new file mode 100644 index 3ecd999..35e3d6f *** a/doc/src/sgml/ref/vacuumdb.sgml --- b/doc/src/sgml/ref/vacuumdb.sgml *** PostgreSQL documentation *** 65,71 command . There is no effective difference between vacuuming and analyzing databases via this utility and via other methods for accessing the !server. --- 65,73 command . There is no effective difference between vacuuming and analyzing databases via this utility and via other methods for accessing the !server. However it does provide additional functionality for generating !statistics in stages, which is useful when optimizing a newly populated !database. diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml new file mode 100644 index 92b8984..d640887 *** a/doc/src/sgml/ref/vacuumdb.sgml --- b/doc/src/sgml/ref/vacuumdb.sgml *** PostgreSQL documentation *** 65,71 command . There is no effective difference between vacuuming and analyzing databases via this utility and via other methods for accessing the !server. --- 65,73 command . There is no effective difference between vacuuming and analyzing databases via this utility and via other methods for accessing the !server. However it does provide additional functionality for generating !statistics in stages, which is useful when optimizing a newly populated !database, and for executing vacuum or analyze commands in parallel. -- 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] remaining open items
On 17/10/15 04:31, Alvaro Herrera wrote: > Robert Haas wrote: >>> The other item on me is the documentation patch by Emre Hasegeli for >>> usage of the inclusion opclass framework in BRIN. I think it needs some >>> slight revision by some native English speaker and I'm not completely in >>> love with the proposed third column in the table it adds, but otherwise >>> is factually correct as far as I can tell. >> >> I'm not clear whether you are asking for help with this, or ...? > > I enlisted the help of Ian Barwick for this one. Revised version of Emre's patch attached, apologies for the delay. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services diff --git a/doc/src/sgml/brin.sgml b/doc/src/sgml/brin.sgml new file mode 100644 index dc5405e..673486f *** a/doc/src/sgml/brin.sgml --- b/doc/src/sgml/brin.sgml *** typedef struct BrinOpcInfo *** 535,541 defined by the user for other data types using equivalent definitions, without having to write any source code; appropriate catalog entries being declared is enough. Note that assumptions about the semantics of operator ! strategies are embedded in the support procedures's source code. --- 535,541 defined by the user for other data types using equivalent definitions, without having to write any source code; appropriate catalog entries being declared is enough. Note that assumptions about the semantics of operator ! strategies are embedded in the support procedure's source code. *** typedef struct BrinOpcInfo *** 566,584 Support Procedure 1 ! function brin_minmax_opcinfo() Support Procedure 2 ! function brin_minmax_add_value() Support Procedure 3 ! function brin_minmax_consistent() Support Procedure 4 ! function brin_minmax_union() Operator Strategy 1 --- 566,584 Support Procedure 1 ! internal function brin_minmax_opcinfo() Support Procedure 2 ! internal function brin_minmax_add_value() Support Procedure 3 ! internal function brin_minmax_consistent() Support Procedure 4 ! internal function brin_minmax_union() Operator Strategy 1 *** typedef struct BrinOpcInfo *** 603,607 --- 603,793 + + + To write an operator class for a complex datatype which has values + included within another type, it's possible to use the inclusion support + procedures alongside the corresponding operators, as shown + in . It requires + only a single additional function, which can be written in any language. + More functions can be defined for additional functionality. All operators + are optional. Some operators require other operators, as shown as + dependencies on the table. + + + + Procedure and Support Numbers for Inclusion Operator Classes + + + + Operator class member + Object + Dependency + + + + + Support Procedure 1 + internal function brin_inclusion_opcinfo() + + + + Support Procedure 2 + internal function brin_inclusion_add_value() + + + + Support Procedure 3 + internal function brin_inclusion_consistent() + + + + Support Procedure 4 + internal function brin_inclusion_union() + + + + Support Procedure 11 + function to merge two elements + + + + Support Procedure 12 + optional function to check whether two elements are mergeable + + + + Support Procedure 13 + optional function to check if an element is contained within another + + + + Support Procedure 14 + optional function to check whether an element is empty + + + + Operator Strategy 1 + operator left-of + Operator Strategy 4 + + + Operator Strategy 2 + operator does-not-extend-to-the-right-of + Operator Strategy 5 + + + Operator Strategy 3 + operator overlaps + + + + Operator Strategy 4 + operator right-of + Operator Strategy 2 + + + Operator Strategy 5 + operator does-not-extend-to-the-right-of + Operator Strategy 1 + + + Operator Strategy 6, 18 + operator same-as-or-equal-to + Operator Strategy 7 + + + Operator Strategy 7, 13, 16, 24, 25 + operator contains-or-equal-to + + + + Operator Strategy 8, 14, 26, 27 + operator is-contained-by-or-equal-to + Operator Strategy 3 + + + Operator Strate
Re: [HACKERS] pg_basebackup, tablespace mapping and path canonicalization
On 29/04/15 09:12, Bruce Momjian wrote: On Fri, Feb 6, 2015 at 08:25:42AM -0500, Robert Haas wrote: On Thu, Feb 5, 2015 at 10:21 PM, Ian Barwick i...@2ndquadrant.com wrote: I stumbled on what appears to be inconsistent handling of double slashes in tablespace paths when using pg_basebackup with the -T/--tablespace-mapping option: ibarwick:postgresql (master)$ mkdir /tmp//foo-old [...] The attached patch adds the missing canonicalization; I can't see any reason not to do this. Thoughts? Seems OK to me. Anyone think differently? Patch applied. Thanks! Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_basebackup, tablespace mapping and path canonicalization
Hi I stumbled on what appears to be inconsistent handling of double slashes in tablespace paths when using pg_basebackup with the -T/--tablespace-mapping option: ibarwick:postgresql (master)$ mkdir /tmp//foo-old ibarwick:postgresql (master)$ $PG_HEAD/psql 'dbname=postgres port=9595' psql (9.5devel) Type help for help. postgres=# CREATE TABLESPACE foo LOCATION '/tmp//foo-old'; CREATE TABLESPACE postgres=# \db List of tablespaces Name| Owner | Location +--+-- foo| ibarwick | /tmp/foo-old pg_default | ibarwick | pg_global | ibarwick | (3 rows) So far so good. However attempting to take a base backup (on the same machine) and remap the tablespace directory: ibarwick:postgresql (master)$ $PG_HEAD/pg_basebackup -p9595 --pgdata=/tmp//backup --tablespace-mapping=/tmp//foo-old=/tmp//foo-new produces the following message: pg_basebackup: directory /tmp/foo-old exists but is not empty which, while undeniably true, is unexpected and could potentially encourage someone to hastily delete /tmp/foo-old after confusing it with the new directory. The double-slash in the old tablespace path is the culprit: ibarwick:postgresql (master)$ $PG_HEAD/pg_basebackup -p9595 --pgdata=/tmp//backup --tablespace-mapping=/tmp/foo-old=/tmp//foo-new NOTICE: pg_stop_backup complete, all required WAL segments have been archived The documentation does state: To be effective, olddir must exactly match the path specification of the tablespace as it is currently defined. which I understood to mean that e.g. tildes would not be expanded, but it's somewhat surprising that the path is not canonicalized in the same way it is pretty much everywhere else (including in CREATE TABLESPACE). The attached patch adds the missing canonicalization; I can't see any reason not to do this. Thoughts? Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training Services diff --git a/src/bin/pg_basebackup/pg_basebackup.c b/src/bin/pg_basebackup/pg_basebackup.c new file mode 100644 index fbf7106..349bd90 *** a/src/bin/pg_basebackup/pg_basebackup.c --- b/src/bin/pg_basebackup/pg_basebackup.c *** tablespace_list_append(const char *arg) *** 199,204 --- 199,207 exit(1); } + canonicalize_path(cell-old_dir); + canonicalize_path(cell-new_dir); + if (tablespace_dirs.tail) tablespace_dirs.tail-next = cell; else -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Docs: CREATE TABLESPACE minor markup fix
Hi A superfluous '/' in an xref tag is producing an unintended '' in the Warning box on this page: http://www.postgresql.org/docs/current/interactive/sql-createtablespace.html Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services diff --git a/doc/src/sgml/ref/create_tablespace.sgml b/doc/src/sgml/ref/create_tablespace.sgml new file mode 100644 index cf6215e..9072d07 *** a/doc/src/sgml/ref/create_tablespace.sgml --- b/doc/src/sgml/ref/create_tablespace.sgml *** CREATE TABLESPACE replaceable class=pa *** 54,60 warning para A tablespace cannot be used independently of the cluster in which it ! is defined; see xref linkend=manage-ag-tablespaces/. /para /warning --- 54,60 warning para A tablespace cannot be used independently of the cluster in which it ! is defined; see xref linkend=manage-ag-tablespaces. /para /warning -- 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] Docs: CREATE TABLESPACE minor markup fix
On 04/02/15 19:02, Fujii Masao wrote: On Wed, Feb 4, 2015 at 5:27 PM, Ian Barwick i...@2ndquadrant.com wrote: Hi A superfluous '/' in an xref tag is producing an unintended '' in the Warning box on this page: http://www.postgresql.org/docs/current/interactive/sql-createtablespace.html I found that logicaldecoding.sgml also has the same typo. Fixed both. Thanks! Thanks! Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql tab completion: fix COMMENT ON ... IS IS IS
On 15/01/01 1:07, Robert Haas wrote:
On Sun, Dec 28, 2014 at 7:44 PM, Ian Barwick i...@2ndquadrant.com wrote:
Currently tab completion for 'COMMENT ON {object} foo IS' will result in the
'IS'
being duplicated up to two times; not a world-shattering issue I know, but
the
fix is trivial and I stumble over it often enough to for it to mildly annoy
me.
Patch attached.
I've noticed that in the past, too. Committed.
Thanks.
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, RemoteDBA, Training Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] psql tab completion: fix COMMENT ON ... IS IS IS
Hi
Currently tab completion for 'COMMENT ON {object} foo IS' will result in the
'IS'
being duplicated up to two times; not a world-shattering issue I know, but the
fix is trivial and I stumble over it often enough to for it to mildly annoy me.
Patch attached.
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
new file mode 100644
index 82c926d..7212015
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
*** psql_completion(const char *text, int st
*** 2130,2141
{
COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
}
! else if ((pg_strcasecmp(prev4_wd, COMMENT) == 0
! pg_strcasecmp(prev3_wd, ON) == 0) ||
! (pg_strcasecmp(prev5_wd, COMMENT) == 0
! pg_strcasecmp(prev4_wd, ON) == 0) ||
! (pg_strcasecmp(prev6_wd, COMMENT) == 0
! pg_strcasecmp(prev5_wd, ON) == 0))
COMPLETE_WITH_CONST(IS);
/* COPY */
--- 2130,2142
{
COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
}
! else if (((pg_strcasecmp(prev4_wd, COMMENT) == 0
! pg_strcasecmp(prev3_wd, ON) == 0) ||
! (pg_strcasecmp(prev5_wd, COMMENT) == 0
! pg_strcasecmp(prev4_wd, ON) == 0) ||
! (pg_strcasecmp(prev6_wd, COMMENT) == 0
! pg_strcasecmp(prev5_wd, ON) == 0))
! pg_strcasecmp(prev_wd, IS) != 0)
COMPLETE_WITH_CONST(IS);
/* COPY */
--
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] Testing DDL deparsing support
On 14/12/07 12:43, Bruce Momjian wrote: On Tue, Dec 2, 2014 at 03:13:07PM -0300, Alvaro Herrera wrote: Robert Haas wrote: On Thu, Nov 27, 2014 at 11:43 PM, Ian Barwick i...@2ndquadrant.com wrote: A simple schedule to demonstrate this is available; execute from the src/test/regress/ directory like this: ./pg_regress \ --temp-install=./tmp_check \ --top-builddir=../../.. \ --dlpath=. \ --schedule=./schedule_ddl_deparse_demo I haven't read the code, but this concept seems good to me. Excellent, thanks. It has the unfortunate weakness that a difference could exist during the *middle* of the regression test run that is gone by the *end* of the run, but our existing pg_upgrade testing has the same weakness, so I guess we can view this as one more reason not to be too aggressive about having regression tests drop the unshared objects they create. Agreed. Not dropping objects also helps test pg_dump itself; the normal procedure there is run the regression tests, then pg_dump the regression database. Objects that are dropped never exercise their corresponding pg_dump support code, which I think is a bad thing. I think we should institute a policy that regression tests must keep the objects they create; maybe not all of them, but at least a sample large enough to cover all interesting possibilities. This causes creation DDL is checked if it is used in the regression database, but what about ALTER and DROP? pg_dump doesn't issue those, except in special cases like inheritance. Sure, pg_dump won't contain ALTER/DROP DDL; we are using pg_dump after replaying the DDL commands to compare the actual state of the database with the expected state. As I'm in the middle of writing these tests, before I go any further do you accept the tests need to be included? Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_regress and --dbname option / multiple databases
Hi pg_regress provides the command line option --dbname, which is described in the help output thusly: --dbname=DBuse database DB (default regression) It does however accept multiple comma separated names and will create a database for each name provided, but AFAICS only ever uses the first database in the list. Is there a reason for this I'm not seeing? Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_regress and --dbname option / multiple databases
On 28/11/14 00:02, Andrew Dunstan wrote: On 11/27/2014 04:12 AM, Ian Barwick wrote: Hi pg_regress provides the command line option --dbname, which is described in the help output thusly: --dbname=DBuse database DB (default regression) It does however accept multiple comma separated names and will create a database for each name provided, but AFAICS only ever uses the first database in the list. Is there a reason for this I'm not seeing? Most of the code is shared between the main regression suite and ecpg's regression suit. The latter uses multiple databases, I believe. Aha, indeed it does. Thanks for the clarification. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Testing DDL deparsing support
. To implement the DDL deparsing, a pseudo-test is executed first, which creates an event trigger and a table in which to store queries captured by the event trigger. Following conclusion of all regression tests, a further test is executed which exports the query table, imports it into the second database and runs pg_dump; the output of this is then compared against the expected output. This test can fail either at the import stage, if the deparsed commands are syntactically incorrect, or at the comparison stage, if the a deparsed command is valid but syntactically different to the original. To facilitate this, some minimal changes to pg_regress itself have been necessary. In the current proof-of-concept it automatically creates (and where appropriate drops) the shadow database used to load the deparsed commands; and also provides a couple of additional tokens to the .source files to provide information otherwise unavailable to the SQL scripts such as the location of pg_dump and the name of the shadow database. A simple schedule to demonstrate this is available; execute from the src/test/regress/ directory like this: ./pg_regress \ --temp-install=./tmp_check \ --top-builddir=../../.. \ --dlpath=. \ --schedule=./schedule_ddl_deparse_demo Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services diff --git a/src/test/regress/expected/.gitignore b/src/test/regress/expected/.gitignore index 93c56c8..2eeaf57 100644 --- a/src/test/regress/expected/.gitignore +++ b/src/test/regress/expected/.gitignore @@ -7,3 +7,7 @@ /misc.out /security_label.out /tablespace.out +/create_function_ddl_demo.out +/deparse_init.out +/deparse_test.out + diff --git a/src/test/regress/expected/create_table_ddl_demo.out b/src/test/regress/expected/create_table_ddl_demo.out new file mode 100644 index 000..2fb3f9c --- /dev/null +++ b/src/test/regress/expected/create_table_ddl_demo.out @@ -0,0 +1,5 @@ +CREATE TABLE hobbies_r ( +nametext, +person text +); +DROP TABLE hobbies_r; diff --git a/src/test/regress/input/create_function_ddl_demo.source b/src/test/regress/input/create_function_ddl_demo.source new file mode 100644 index 000..9982a73 --- /dev/null +++ b/src/test/regress/input/create_function_ddl_demo.source @@ -0,0 +1,4 @@ +CREATE FUNCTION check_foreign_key () + RETURNS trigger + AS '@libdir@/refint@DLSUFFIX@' + LANGUAGE C; \ No newline at end of file diff --git a/src/test/regress/input/deparse_init.source b/src/test/regress/input/deparse_init.source new file mode 100644 index 000..2a53cc3 --- /dev/null +++ b/src/test/regress/input/deparse_init.source @@ -0,0 +1,18 @@ +-- +-- DEPARSE_INIT +-- + +CREATE TABLE deparse_test_commands ( + id SERIAL PRIMARY KEY, + command TEXT +); + +CREATE FUNCTION deparse_test_ddl_command_end() + RETURNS event_trigger + LANGUAGE C +AS '@libdir@/regress@DLSUFFIX@', 'deparse_test_ddl_command_end'; + +/* This should come last - we don't want to log anything defined here */ +CREATE EVENT TRIGGER deparse_test_trg_ddl_command_end + ON ddl_command_end + EXECUTE PROCEDURE deparse_test_ddl_command_end(); \ No newline at end of file diff --git a/src/test/regress/input/deparse_test.source b/src/test/regress/input/deparse_test.source new file mode 100644 index 000..049924f --- /dev/null +++ b/src/test/regress/input/deparse_test.source @@ -0,0 +1,8 @@ +--- +--- DEPARSE_TEST +--- + +\copy (SELECT command || ';' FROM deparse_test_commands ORDER BY id) TO './sql/deparse_dump.sql' +\! @psqldir@/psql --dbname=@deparse_test_db@ ./sql/deparse_dump.sql /dev/null + +\! @psqldir@/pg_dump --schema-only --no-owner --no-privileges -Fp @deparse_test_db@ diff --git a/src/test/regress/output/create_function_ddl_demo.source b/src/test/regress/output/create_function_ddl_demo.source new file mode 100644 index 000..58dba52 --- /dev/null +++ b/src/test/regress/output/create_function_ddl_demo.source @@ -0,0 +1,4 @@ +CREATE FUNCTION check_foreign_key () + RETURNS trigger + AS '@libdir@/refint@DLSUFFIX@' + LANGUAGE C; diff --git a/src/test/regress/output/deparse_init.source b/src/test/regress/output/deparse_init.source new file mode 100644 index 000..10cc234 --- /dev/null +++ b/src/test/regress/output/deparse_init.source @@ -0,0 +1,15 @@ +-- +-- DEPARSE_INIT +-- +CREATE TABLE deparse_test_commands ( + id SERIAL PRIMARY KEY, + command TEXT +); +CREATE FUNCTION deparse_test_ddl_command_end() + RETURNS event_trigger + LANGUAGE C +AS '@libdir@/regress@DLSUFFIX@', 'deparse_test_ddl_command_end'; +/* This should come last - we don't want to log anything defined here */ +CREATE EVENT TRIGGER deparse_test_trg_ddl_command_end + ON ddl_command_end + EXECUTE PROCEDURE deparse_test_ddl_command_end(); diff --git a/src/test/regress/output/deparse_test.source b/src/test/regress/output/deparse_test.source new file mode 100644 index 000..2d5b072 --- /dev/null +++ b
Re: [HACKERS] Comment header for src/test/regress/regress.c
On 14/11/21 22:10, Heikki Linnakangas wrote: On 11/21/2014 06:23 AM, Ian Barwick wrote: I thought it might be useful to add a few words at the top of 'src/test/regress/regress.c' to explain what it does and to help differentiate it from 'pg_regress.c' and 'pg_regress_main.c'. Makes sense, committed. I remember being a bit confused on that myself, when first reading the pg_regress code. Thanks! Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Comment header for src/test/regress/regress.c
I thought it might be useful to add a few words at the top of 'src/test/regress/regress.c' to explain what it does and to help differentiate it from 'pg_regress.c' and 'pg_regress_main.c'. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services diff --git a/src/test/regress/regress.c b/src/test/regress/regress.c new file mode 100644 index 1487171..be27416 *** a/src/test/regress/regress.c --- b/src/test/regress/regress.c *** *** 1,5 ! /* * src/test/regress/regress.c */ #include postgres.h --- 1,17 ! /* ! * ! * regress.c ! * Code for various C-language functions defined as part of the ! * regression tests. ! * ! * This code is released under the terms of the PostgreSQL License. ! * ! * Portions Copyright (c) 1996-2014, PostgreSQL Global Development Group ! * Portions Copyright (c) 1994, Regents of the University of California ! * * src/test/regress/regress.c + * + *- */ #include postgres.h -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql tab completion: \c [ dbname [ username ] ]
On 10/11/14 22:20, Robert Haas wrote: On Sun, Nov 9, 2014 at 6:13 PM, Ian Barwick i...@2ndquadrant.com wrote: Attached is a mighty trivial patch to extend psql tab completion for \c / \connect to generate a list of role names, as lack thereof was annoying me recently and I can't see any downside to doing this. Committed, thanks. Many thanks! Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] psql tab completion: \c [ dbname [ username ] ]
Hi Attached is a mighty trivial patch to extend psql tab completion for \c / \connect to generate a list of role names, as lack thereof was annoying me recently and I can't see any downside to doing this. The patch is a whole two lines so I haven't submitted it to the next commitfest but will happily do so if appropriate. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c new file mode 100644 index 886188c..56dc688 *** a/src/bin/psql/tab-complete.c --- b/src/bin/psql/tab-complete.c *** psql_completion(const char *text, int st *** 3704,3709 --- 3704,3711 } else if (strcmp(prev_wd, \\connect) == 0 || strcmp(prev_wd, \\c) == 0) COMPLETE_WITH_QUERY(Query_for_list_of_databases); + else if (strcmp(prev2_wd, \\connect) == 0 || strcmp(prev2_wd, \\c) == 0) + COMPLETE_WITH_QUERY(Query_for_list_of_roles); else if (strncmp(prev_wd, \\da, strlen(\\da)) == 0) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_aggregates, 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] pgaudit - an auditing extension for PostgreSQL
On 14/10/09 7:06, Stephen Frost wrote: * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: On Tue, Oct 7, 2014 at 1:24 PM, Simon Riggs si...@2ndquadrant.com wrote: I hope we can get pgAudit in as a module for 9.5. I also hope that it will stimulate the requirements/funding of further work in this area, rather than squash it. My feeling is we have more examples of feature sets that grow over time (replication, view handling, hstore/JSONB etc) than we have examples of things languishing in need of attention (partitioning). +1 To this point, specifically, I'll volunteer to find time in Novemeber to review pgAudit for inclusion as a contrib module. I feel a bit responsible for it not being properly reviewed earlier due to my upgrade and similar concerns. Perhaps the latest version should be posted and added to the commitfest for 2014-10 and I'll put myself down as a reviewer..? I don't see it there now. I don't mean to be dismissive by suggesting it be added to the commitfest- I honestly don't see myself having time before November given the other things I'm involved in right now and pgConf.eu happening in a few weeks. Thanks :) We're updating pgAudit for submission this for the upcoming commitfest, it will be added within the next few days. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 14/09/02 12:24, Craig Ringer wrote: On 09/02/2014 08:09 AM, Neil Tiffin wrote: (...) That should be enough alone to suggest postgreSQL start working on a modern, in core, fast, fully supported language. I couldn't disagree more. If we were to implement anything, it'd be PL/PSM (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and quirky as anything else the SQL committee has brought forth, but it's at least a standard(ish) language. For reference, and without wading into the general debate, there is an existing, albeit outdated and dormant PL/PSM implementation: http://pgfoundry.org/frs/?group_id=1000238 http://postgres.cz/wiki/SQL/PSM_Manual From my (limited) experience with the MySQL variant, it makes PL/pgSQL look positively concise and elegant. Though that's just my subjective opinion (possibly coloured by the particular implementation) and not necessarily a pro/contra argument ;). Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RETURNING PRIMARY KEY syntax extension
On 14/07/01 23:13, Robert Haas wrote: On Tue, Jul 1, 2014 at 8:00 AM, Rushabh Lathia rushabh.lat...@gmail.com wrote: .) In map_primary_key_to_list() patch using INDEX_ATTR_BITMAP_IDENTITY_KEY bitmap to get the keycols. In IndexAttrBitmapKind there is also INDEX_ATTR_BITMAP_KEY, so was confuse between INDEX_ATTR_BITMAP_KEY and INDEX_ATTR_BITMAP_IDENTITY_KEY and also haven't found related comments in the code. Later with use of testcase and debugging found confirmed that INDEX_ATTR_BITMAP_IDENTITY_KEY only returns the Primary key. Actually, that depends on how REPLICA IDENTITY is set. IOW, you can't assume that will give you the primary key. Damn, fooled by the name. Thanks for the info; I'll rework the patch accordingly. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RETURNING PRIMARY KEY syntax extension
On 02/07/14 15:16, Ian Barwick wrote:
On 14/07/01 23:13, Robert Haas wrote:
On Tue, Jul 1, 2014 at 8:00 AM, Rushabh Lathia rushabh.lat...@gmail.com wrote:
.) In map_primary_key_to_list() patch using INDEX_ATTR_BITMAP_IDENTITY_KEY
bitmap to get the keycols. In IndexAttrBitmapKind there is also
INDEX_ATTR_BITMAP_KEY, so was confuse between INDEX_ATTR_BITMAP_KEY and
INDEX_ATTR_BITMAP_IDENTITY_KEY and also haven't found related comments in
the code. Later with use of testcase and debugging found confirmed that
INDEX_ATTR_BITMAP_IDENTITY_KEY only returns the Primary key.
Actually, that depends on how REPLICA IDENTITY is set. IOW, you can't
assume that will give you the primary key.
Damn, fooled by the name. Thanks for the info; I'll rework the patch
accordingly.
Attached version implements an IndexAttrBitmapKind
INDEX_ATTR_BITMAP_PRIMARY_KEY,
which will return the primary key column(s). Note this would require a catalog
version bump.
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 74ea907..45295d1 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -25,7 +25,7 @@ PostgreSQL documentation
DELETE FROM [ ONLY ] replaceable class=PARAMETERtable_name/replaceable [ * ] [ [ AS ] replaceable class=parameteralias/replaceable ]
[ USING replaceable class=PARAMETERusing_list/replaceable ]
[ WHERE replaceable class=PARAMETERcondition/replaceable | WHERE CURRENT OF replaceable class=PARAMETERcursor_name/replaceable ]
-[ RETURNING * | replaceable class=parameteroutput_expression/replaceable [ [ AS ] replaceable class=parameteroutput_name/replaceable ] [, ...] ]
+[ RETURNING * | replaceable class=parameteroutput_expression/replaceable [ [ AS ] replaceable class=parameteroutput_name/replaceable ] [, ...] | PRIMARY KEY ]
/synopsis
/refsynopsisdiv
@@ -182,6 +182,17 @@ DELETE FROM [ ONLY ] replaceable class=PARAMETERtable_name/replaceable [ *
/para
/listitem
/varlistentry
+
+ varlistentry
+termliteralPRIMARY KEY/literal/term
+listitem
+ para
+ Returns the table's primary key column(s) after each row is deleted.
+ Cannot be combined with an replaceable class=PARAMETERoutput_expression/replaceable.
+ /para
+/listitem
+ /varlistentry
+
/variablelist
/refsect1
@@ -208,7 +219,9 @@ DELETE replaceable class=parametercount/replaceable
clause, the result will be similar to that of a commandSELECT/
statement containing the columns and values defined in the
literalRETURNING/ list, computed over the row(s) deleted by the
- command.
+ command. literalPRIMARY KEY/ can be specified to return the
+ primary key value(s) for each deleted row. An error will be raised
+ if the table does not have a primary key.
/para
/refsect1
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index a3cccb9..9fbd859 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -24,7 +24,7 @@ PostgreSQL documentation
[ WITH [ RECURSIVE ] replaceable class=parameterwith_query/replaceable [, ...] ]
INSERT INTO replaceable class=PARAMETERtable_name/replaceable [ ( replaceable class=PARAMETERcolumn_name/replaceable [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { replaceable class=PARAMETERexpression/replaceable | DEFAULT } [, ...] ) [, ...] | replaceable class=PARAMETERquery/replaceable }
-[ RETURNING * | replaceable class=parameteroutput_expression/replaceable [ [ AS ] replaceable class=parameteroutput_name/replaceable ] [, ...] ]
+[ RETURNING * | replaceable class=parameteroutput_expression/replaceable [ [ AS ] replaceable class=parameteroutput_name/replaceable ] [, ...] | PRIMARY KEY ]
/synopsis
/refsynopsisdiv
@@ -65,7 +65,9 @@ INSERT INTO replaceable class=PARAMETERtable_name/replaceable [ ( replace
defaults, such as a serial sequence number. However, any expression
using the table's columns is allowed. The syntax of the
literalRETURNING/ list is identical to that of the output list
- of commandSELECT/.
+ of commandSELECT/. Alternatively, literalPRIMARY KEY/ will
+ return the primary key value(s) for each inserted row. An error will
+ be raised if the table does not have a primary key.
/para
para
@@ -186,6 +188,17 @@ INSERT INTO replaceable class=PARAMETERtable_name/replaceable [ ( replace
/para
/listitem
/varlistentry
+
+ varlistentry
+termliteralPRIMARY KEY/literal/term
+listitem
+ para
+ Returns the table's primary key column(s) after each row is inserted.
+ Cannot be combined with an replaceable class=PARAMETERoutput_expression/replaceable.
+ /para
+/listitem
+ /varlistentry
+
/variablelist
/refsect1
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 35b0699..27c49c4 100644
Re: [HACKERS] RETURNING PRIMARY KEY syntax extension
On 01/07/14 21:00, Rushabh Lathia wrote: I spent some more time on the patch and here are my review comments. .) Patch gets applied through patch -p1 (git apply fails) .) trailing whitespace in the patch at various places Not sure where you see this, unless it's in the tests, where it's required. .) Unnecessary new line + and - in the patch. (src/backend/rewrite/rewriteManip.c::getInsertSelectQuery()) (src/include/rewrite/rewriteManip.h) Fixed. .) patch has proper test coverage and regression running cleanly. .) In map_primary_key_to_list() patch using INDEX_ATTR_BITMAP_IDENTITY_KEY bitmap to get the keycols. In IndexAttrBitmapKind there is also INDEX_ATTR_BITMAP_KEY, so was confuse between INDEX_ATTR_BITMAP_KEY and INDEX_ATTR_BITMAP_IDENTITY_KEY and also haven't found related comments in the code. Later with use of testcase and debugging found confirmed that INDEX_ATTR_BITMAP_IDENTITY_KEY only returns the Primary key. Revised patch version (see other mail) fixes this by introducing INDEX_ATTR_BITMAP_PRIMARY_KEY. .) At present in patch when RETURNING PRIMARY KEY is used on table having no primary key it throw an error. If I am not wrong JDBC will be using that into getGeneratedKeys(). Currently this feature is implemented in the JDBC driver by appending RETURNING * to the supplied statement. With this implementation it will replace RETURNING * with RETURNING PRIMARY KEY, right ? So just wondering what JDBC expect getGeneratedKeys() to return when query don't have primary key and user called executeUpdate() with Statement.RETURN_GENERATED_KEYS? I looked at JDBC specification but its not clear what it will return when table don't have keys. Can you please let us know your finding on this ? The spec [*] is indeed frustratingly vague: The method Statement.getGeneratedKeys, which can be called to retrieve the generated value, returns a ResultSet object with a column for each automatically generated value. The methods execute, executeUpdate or Connection.prepareStatement accept an optional parameter, which can be used to indicate that any auto generated values should be returned when the statement is executed or prepared. [*] http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jdbc4.1-fr-spec.pdf I understand this to mean that no rows will be returned if no auto-generated values are not present. As-is, the patch will raise an error if the target table does not have a primary key, which makes sense from the point of view of the proposed syntax, but which will make it impossible for the JDBC driver to implement the above understanding of the spec (i.e. return nothing if no primary key exists). It would be simple enough not to raise an error in this case, but that means the query would be effectively failing silently and I don't think that's desirable behaviour. A better solution would be to have an optional IF EXISTS clause: RETURNING PRIMARY KEY [ IF EXISTS ] which would be easy enough to implement. Thoughts? Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgaudit - an auditing extension for PostgreSQL
On 14/06/25 23:36, Stephen Frost wrote: Other databases have had this kind of capability as a matter of course for decades- we are far behind in this area. On a related note, MySQL/MariaDB have had some sort of auditing capability for, well, months. By no means as sophisticated as some of the others, but still more than nothing. https://www.mysql.com/products/enterprise/audit.html https://mariadb.com/kb/en/about-the-mariadb-audit-plugin/ Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RETURNING PRIMARY KEY syntax extension
On 27/06/14 09:09, Tom Dunstan wrote: On 27 June 2014 06:14, Gavin Flower gavinflo...@archidevsys.co.nz mailto:gavinflo...@archidevsys.co.nz wrote: On 27/06/14 00:12, Rushabh Lathia wrote: INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK') returning primary key, dname; I think allowing other columns with PRIMARY KEY would be more useful syntax. Even in later versions if we want to extend this syntax to return UNIQUE KEY, SEQUENCE VALUES, etc.. comma separation syntax will be more handy. I agree 100%. If the query is being hand-crafted, what's to stop the query writer from just listing the id columns in the returning clause? And someone specifying RETURNING * is getting all the columns anyway. The target use-case for this feature is a database driver that has just done an insert and doesn't know what the primary key columns are - in that case mixing them with any other columns is actually counter-productive as the driver won't know which columns are which. What use cases are there where the writer of the query knows enough to write specific columns in the RETURNING clause but not enough to know which column is the id column? Consistency is nice, and I can understand wanting to treat the PRIMARY KEY bit as just another set of columns in the list to return, but I'd hate to see this feature put on the back-burner to support use-cases that are already handled by the current RETURNING feature. Maybe it's easy to do, though.. I haven't looked into the implementation at all. Normal columns are injected into the query's returning list at parse time, whereas this version of the patch handles expansion of PRIMARY KEY at the rewrite stage, which would make handling a mix of PRIMARY KEY and normal output expressions somewhat tricky to handle. (In order to maintain the columns in their expected position you'd have to add some sort of placeholder/dummy TargetEntry to the returning list at parse time, then rewrite it later with the expanded primary key columns, or something equally messy). On the other hand, it should be fairly straightforward to handle a list of keywords for expansion (e.g. RETURNING PRIMARY KEY, UNIQUE KEYS, SEQUENCE VALUES) should the need arise. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RETURNING PRIMARY KEY syntax extension
Hi On 14/06/25 15:13, Rushabh Lathia wrote: Hello All, I assigned my self as reviewer of the patch. I gone through the mail chain discussion and in that question has been raised about the feature and its implementation, so would like to know what is the current status of this project/patch. Regards, I'll be submitting a revised version of this patch very shortly. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RETURNING PRIMARY KEY syntax extension
On 25/06/14 16:04, Ian Barwick wrote:
Hi
On 14/06/25 15:13, Rushabh Lathia wrote:
Hello All,
I assigned my self as reviewer of the patch. I gone through the
mail chain discussion and in that question has been raised about
the feature and its implementation, so would like to know what is
the current status of this project/patch.
Regards,
I'll be submitting a revised version of this patch very shortly.
Revised version of the patch attached, which implements the expansion
of primary key in the rewrite phase per Tom Lane's suggestion upthread [*]
[*] http://www.postgresql.org/message-id/28583.1402325...@sss.pgh.pa.us
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 74ea907..45295d1 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -25,7 +25,7 @@ PostgreSQL documentation
DELETE FROM [ ONLY ] replaceable class=PARAMETERtable_name/replaceable [ * ] [ [ AS ] replaceable class=parameteralias/replaceable ]
[ USING replaceable class=PARAMETERusing_list/replaceable ]
[ WHERE replaceable class=PARAMETERcondition/replaceable | WHERE CURRENT OF replaceable class=PARAMETERcursor_name/replaceable ]
-[ RETURNING * | replaceable class=parameteroutput_expression/replaceable [ [ AS ] replaceable class=parameteroutput_name/replaceable ] [, ...] ]
+[ RETURNING * | replaceable class=parameteroutput_expression/replaceable [ [ AS ] replaceable class=parameteroutput_name/replaceable ] [, ...] | PRIMARY KEY ]
/synopsis
/refsynopsisdiv
@@ -182,6 +182,17 @@ DELETE FROM [ ONLY ] replaceable class=PARAMETERtable_name/replaceable [ *
/para
/listitem
/varlistentry
+
+ varlistentry
+termliteralPRIMARY KEY/literal/term
+listitem
+ para
+ Returns the table's primary key column(s) after each row is deleted.
+ Cannot be combined with an replaceable class=PARAMETERoutput_expression/replaceable.
+ /para
+/listitem
+ /varlistentry
+
/variablelist
/refsect1
@@ -208,7 +219,9 @@ DELETE replaceable class=parametercount/replaceable
clause, the result will be similar to that of a commandSELECT/
statement containing the columns and values defined in the
literalRETURNING/ list, computed over the row(s) deleted by the
- command.
+ command. literalPRIMARY KEY/ can be specified to return the
+ primary key value(s) for each deleted row. An error will be raised
+ if the table does not have a primary key.
/para
/refsect1
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index a3cccb9..9fbd859 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -24,7 +24,7 @@ PostgreSQL documentation
[ WITH [ RECURSIVE ] replaceable class=parameterwith_query/replaceable [, ...] ]
INSERT INTO replaceable class=PARAMETERtable_name/replaceable [ ( replaceable class=PARAMETERcolumn_name/replaceable [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { replaceable class=PARAMETERexpression/replaceable | DEFAULT } [, ...] ) [, ...] | replaceable class=PARAMETERquery/replaceable }
-[ RETURNING * | replaceable class=parameteroutput_expression/replaceable [ [ AS ] replaceable class=parameteroutput_name/replaceable ] [, ...] ]
+[ RETURNING * | replaceable class=parameteroutput_expression/replaceable [ [ AS ] replaceable class=parameteroutput_name/replaceable ] [, ...] | PRIMARY KEY ]
/synopsis
/refsynopsisdiv
@@ -65,7 +65,9 @@ INSERT INTO replaceable class=PARAMETERtable_name/replaceable [ ( replace
defaults, such as a serial sequence number. However, any expression
using the table's columns is allowed. The syntax of the
literalRETURNING/ list is identical to that of the output list
- of commandSELECT/.
+ of commandSELECT/. Alternatively, literalPRIMARY KEY/ will
+ return the primary key value(s) for each inserted row. An error will
+ be raised if the table does not have a primary key.
/para
para
@@ -186,6 +188,17 @@ INSERT INTO replaceable class=PARAMETERtable_name/replaceable [ ( replace
/para
/listitem
/varlistentry
+
+ varlistentry
+termliteralPRIMARY KEY/literal/term
+listitem
+ para
+ Returns the table's primary key column(s) after each row is inserted.
+ Cannot be combined with an replaceable class=PARAMETERoutput_expression/replaceable.
+ /para
+/listitem
+ /varlistentry
+
/variablelist
/refsect1
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 35b0699..27c49c4 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -29,7 +29,7 @@ UPDATE [ ONLY ] replaceable class=PARAMETERtable_name/replaceable [ * ] [
} [, ...]
[ FROM replaceable class=PARAMETERfrom_list/replaceable ]
[ WHERE replaceable class=PARAMETERcondition/replaceable | WHERE CURRENT
Re: [HACKERS] tab completion for setting search_path
On 23/06/14 00:58, Andres Freund wrote: On 2014-05-05 09:10:17 -0700, Jeff Janes wrote: On Sat, May 3, 2014 at 1:11 AM, Andres Freund and...@2ndquadrant.comwrote: On 2014-05-03 00:13:45 -0700, Jeff Janes wrote: On Friday, May 2, 2014, Jeff Janes jeff.ja...@gmail.com wrote: Why should we exclude system schemata? That seems more likely to be confusing than helpful? I can see a point in excluding another backend's temp tables, but otherwise? I've personally never had a need to set the search_path to a system schema, and I guess I was implicitly modelling this on what is returned by \dn, not by \dnS. I wouldn't object much to including them; that would be better than not having any completion. I just don't see much point. And now playing a bit with the system ones, I think it would be more confusing to offer them. pg_catalog and pg_temp_appropriate always get searched, whether you put them in the search_path or not. I thought about committing this but couldn't get over this bit. If you type SELECT * FROM pg_cattab it'll get autocompleted to pg_catalog.pg_ and pg_temptab will list all the temp schemas including the numeric and toast ones. So we have precedent for *not* bothering about excluding any schemas. I don't think we should start doing so in a piecemal fashion in an individual command's completion. There is an exception of sorts already for system schemas, in that although SELECT * FROM ptab will list the system schemas, it will not list any tables from them, and won't until SELECT * FROM pg_tab is entered (see note in tab-completion.c around line 3722). Personally I'd be mildly annoyed if every SET search_path TO ptab resulted in all the system schemas being displayed when all I want is public; how about having these listed only once pg_ is entered, i.e. SET search_path TO pg_tab? Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] replication commands and log_statements
On 12/06/14 20:37, Fujii Masao wrote: On Wed, Jun 11, 2014 at 11:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Andres Freund and...@2ndquadrant.com writes: Your wish just seems like a separate feature to me. Including replication commands in 'all' seems correct independent of the desire for a more granular control. No, I think I've got to vote with the other side on that. The reason we can have log_statement as a scalar progression none ddl mod all is that there's little visible use-case for logging DML but not DDL, nor for logging SELECTS but not INSERT/UPDATE/DELETE. However, logging replication commands seems like something people would reasonably want an orthogonal control for. There's no nice way to squeeze such a behavior into log_statement. I guess you could say that log_statement treats replication commands as if they were DDL, but is that really going to satisfy users? I think we should consider log_statement to control logging of SQL only, and invent a separate GUC (or, in the future, likely more than one GUC?) for logging of replication activity. Seems reasonable. OK. The attached patch adds log_replication_command parameter which causes replication commands to be logged. I added this to next CF. A quick review: - Compiles against HEAD - Works as advertised - Code style looks fine A couple of suggestions: - minor rewording for the description, mentioning that statements will still be logged as DEBUG1 even if parameter set to 'off' (might prevent reports of the kind I set it to 'off', why am I still seeing log entries?). para Causes each replication command to be logged in the server log. See xref linkend=protocol-replication for more information about replication commands. The default value is literaloff/. When set to literaloff/, commands will be logged at log level literalDEBUG1/literal. Only superusers can change this setting. /para - I feel it would be more consistent to use the plural form for this parameter, i.e. log_replication_commands, in line with log_lock_waits, log_temp_files, log_disconnections etc. - It might be an idea to add a cross-reference to this parameter from the Streaming Replication Protocol page: http://www.postgresql.org/docs/devel/static/protocol-replication.html Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Possible index issue on 9.5 slave
Hi I've just run into an index issue on 9.5 HEAD on a slave (master and slave both compiled from 66802246e22d51858cd543877fcfddf24e6812f2); details below (I have only found one index on the slave where the issue occurs so far). The setup is admittedly slightly unusual; master is OS X 10.7.5, slave is CentOS on a Virtualbox guest VM on the same system. The issue only occurs with this combination of master and slave; I haven't been able to reproduce it with master and slave running natively on OS X, or with a Linux guest VM on a Linux machine. I have reproduced it several times on the OS X/Linux guest VM combination. I can't dig any further into this at the moment but can happily provide further details etc. Master == $ uname -a Darwin nara.local 11.4.2 Darwin Kernel Version 11.4.2: Thu Aug 23 16:25:48 PDT 2012; root:xnu-1699.32.7~1/RELEASE_X86_64 x86_64 tgg_current= SELECT version(); version -- PostgreSQL 9.5devel on x86_64-apple-darwin11.4.2, compiled by gcc (MacPorts gcc48 4.8.2_2) 4.8.2, 64-bit (1 row) tgg_current= select user_id, login from tgg_user where login ='admin'; user_id | login -+--- 1 | admin (1 row) Slave = $ uname -a Linux localhost.localdomain 2.6.32-358.el6.x86_64 #1 SMP Fri Feb 22 00:31:26 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux tgg_current= select version(); version - PostgreSQL 9.5devel on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit (1 row) tgg_current= select user_id,login from tgg_user where login ='admin'; user_id | login -+--- (0 rows) tgg_current= explain select user_id,login from tgg_user where login ='admin'; QUERY PLAN Index Scan using tgg_user_login_key on tgg_user (cost=0.28..8.30 rows=1 width=15) Index Cond: ((login)::text = 'admin'::text) Planning time: 0.105 ms (3 rows) tgg_current= set enable_bitmapscan=off; SET tgg_current= set enable_indexscan =off; SET tgg_current= select user_id,login from tgg_user where login ='admin'; user_id | login -+--- 1 | admin (1 row) tgg_current= \d tgg_user_login_key Index epp.tgg_user_login_key Column | Type | Definition +---+ login | character varying(32) | login unique, btree, for table epp.tgg_user Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Possible index issue on 9.5 slave
On 19/06/14 11:58, Peter Geoghegan wrote: On Wed, Jun 18, 2014 at 6:54 PM, Ian Barwick i...@2ndquadrant.com wrote: I've just run into an index issue on 9.5 HEAD on a slave (master and slave both compiled from 66802246e22d51858cd543877fcfddf24e6812f2); details below (I have only found one index on the slave where the issue occurs so far). Would you mind running my btreecheck tool on both systems? That might shed some light on this. You can get it from: http://www.postgresql.org/message-id/cam3swzrtv+xmrwlwq6c-x7czvwavfdwfi4st1zz4ddgfh4y...@mail.gmail.com . I suggest running bt_parent_index_verify() and bt_leftright_verify() on all indexes on both systems. It shouldn't take too long. Interesting, I'll take a look later. Thanks Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Possible index issue on 9.5 slave
On 19/06/14 12:30, Peter Geoghegan wrote: On Wed, Jun 18, 2014 at 8:09 PM, Ian Barwick i...@2ndquadrant.com wrote: Interesting, I'll take a look later. I'm pretty suspicious of incompatibilities that may exist between the two sets of OS collations involved here. We aren't very clear on the extent to which what you're doing is supported, but it's certainly the case that bttextcmp()/varstr_cmp()/strcoll() return values must be immutable between the two systems. Still, it should be possible to determine if that's the problem using btreecheck. Do you get perfectly consistent answers between the two when you ORDER BY login? Hmm, nope, different sort order. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Possible index issue on 9.5 slave
On 19/06/14 12:35, Tom Lane wrote: Peter Geoghegan p...@heroku.com writes: On Wed, Jun 18, 2014 at 8:09 PM, Ian Barwick i...@2ndquadrant.com wrote: Interesting, I'll take a look later. I'm pretty suspicious of incompatibilities that may exist between the two sets of OS collations involved here. We aren't very clear on the extent to which what you're doing is supported, but it's certainly the case that bttextcmp()/varstr_cmp()/strcoll() return values must be immutable between the two systems. Oooh, I'll bet that's exactly it. Is the database using UTF8 encoding and a non-C locale? Yup, that is indeed the case. It's well known that OS X's UTF8 locales sort nothing at all like the supposedly equivalent locales on other systems. True, that. A different sort order wouldn't have surprised me, but the failure to return an extant row had me thinking there was something awry with the laptop causing file corruption (it's getting on in years and has been bashed about a bit). Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [REVIEW] psql tab completion for DROP TRIGGER/RULE and ALTER TABLE ... DISABLE/ENABLE
Andreas Karlsson (andr...@proxel.se) wrote:
Hi,
When benchmarking an application I got annoyed at how basic the tab
completion for ALTER TABLE ... DISABLE/ENABLE TRIGGER and DROP TRIGGER
is. So here is a patch improving the tab completion around triggers. For
consistency I have also added the same completions to rules since their
DDL is almost identical.
Thanks for this patch; I'm playing around with rules at the moment and it was
very useful. A quick review:
- applies cleanly to HEAD
- does what it claims, i.e. adds tab completion support for this syntax:
ALTER TABLE table { ENABLE | DISABLE } [ ALWAYS | REPLICA ] { RULE |
TRIGGER } rule_or_trigger
DROP TRIGGER trigger ON relation { CASCADE | RESTRICT }
DROP RULE rule ON relation { CASCADE | RESTRICT }
- code style is consistent with the project style
One issue - the table's internal triggers will also be listed. which can result
in
something like this:
database= ALTER TABLE object_version DISABLE TRIGGER TAB
RI_ConstraintTrigger_a_1916401 RI_ConstraintTrigger_a_1916422
RI_ConstraintTrigger_c_1916358
RI_ConstraintTrigger_a_1916402 RI_ConstraintTrigger_c_1916238
RI_ConstraintTrigger_c_1916359
RI_ConstraintTrigger_a_1916406 RI_ConstraintTrigger_c_1916239
RI_ConstraintTrigger_c_1916398
RI_ConstraintTrigger_a_1916407 RI_ConstraintTrigger_c_1916263
RI_ConstraintTrigger_c_1916399
RI_ConstraintTrigger_a_1916411 RI_ConstraintTrigger_c_1916264
RI_ConstraintTrigger_c_1916478
RI_ConstraintTrigger_a_1916412 RI_ConstraintTrigger_c_1916298
RI_ConstraintTrigger_c_1916479
RI_ConstraintTrigger_a_1916416 RI_ConstraintTrigger_c_1916299
RI_ConstraintTrigger_c_1916513
RI_ConstraintTrigger_a_1916417 RI_ConstraintTrigger_c_1916328
RI_ConstraintTrigger_c_1916514
RI_ConstraintTrigger_a_1916421 RI_ConstraintTrigger_c_1916329
ts_vector_update
This is a bit of an extreme case, but I don't think manually manipulating
internal triggers (which can only be done as a superuser) is a common enough
operation to justify their inclusion. I suggest adding
'AND tgisinternal is FALSE' to 'Query_for_trigger_of_table' to hide them.
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [REVIEW] psql tab completion for DROP TRIGGER/RULE and ALTER TABLE ... DISABLE/ENABLE
On 14/06/18 7:51, Andreas Karlsson wrote: On 06/17/2014 01:36 PM, Ian Barwick wrote: One issue - the table's internal triggers will also be listed. which can result in something like this: This is a bit of an extreme case, but I don't think manually manipulating internal triggers (which can only be done as a superuser) is a common enough operation to justify their inclusion. I suggest adding 'AND tgisinternal is FALSE' to 'Query_for_trigger_of_table' to hide them. Good suggestion. I have attached a patch which filters out the internal triggers, both for ALTER TABLE and DROP TRIGGER. I am not entirely sure about the DROP TRIGGER case but I think I prefer no auto completion of RI triggers. Thanks, looks good. Another reason for not autocompleting RI triggers is that the names are all auto-generated; on the offchance you are manually manipulating them individually, you'd have to have a pretty good idea of which ones you're working with anyway. Personally I think this patch could go into 9.4, as it's not introducing any new features and doesn't depend on any 9.5 syntax. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doing better at HINTing an appropriate column within errorMissingColumn()
On 14/06/17 8:31, Peter Geoghegan wrote: On Mon, Jun 16, 2014 at 4:04 PM, Josh Berkus j...@agliodbs.com wrote: Question: How should we handle the issues with East Asian languages (i.e. Japanese, Chinese) and this Hint? Should we just avoid hinting for a selected list of languages which don't work well with levenshtein? If so, how do we get that list? I think that how useful Levenshtein distance is for users based in east Asia generally, and how useful this patch is to those users are two distinct questions. I have no idea how common it is for Japanese users to just use Roman characters as table and attribute names. Since they're very probably already writing application code that uses Roman characters (except in the comments, user strings and so on), it might make sense to do the same in the database. I would welcome further input on that question. I don't know what the trends are in the real world. From what I've seen in the wild in Japan, Roman/ASCII characters are widely used for object/attribute names, as generally it's much less hassle than switching between input methods, dealing with different encodings etc. The only place where I've seen Japanese characters widely used is in tutorials, examples etc. However that's only my personal observation for one particular non-Roman language. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doing better at HINTing an appropriate column within errorMissingColumn()
On 14/06/17 9:53, Tom Lane wrote: Michael Paquier michael.paqu...@gmail.com writes: On Tue, Jun 17, 2014 at 9:30 AM, Ian Barwick i...@2ndquadrant.com wrote: From what I've seen in the wild in Japan, Roman/ASCII characters are widely used for object/attribute names, as generally it's much less hassle than switching between input methods, dealing with different encodings etc. The only place where I've seen Japanese characters widely used is in tutorials, examples etc. However that's only my personal observation for one particular non-Roman language. And I agree to this remark, that's a PITA to manage database object names with Japanese characters directly. I have ever seen some applications using such ways to define objects though in the past, not *that* many I concur.. What exactly is the rationale for thinking that Levenshtein distance is useless in non-Roman alphabets? AFAIK it just counts insertions and deletions of characters, which seems like a concept rather independent of what those characters are. With Japanese (which doesn't have an alphabet, but two syllabaries and a bunch of logographic characters), Levenshtein distance is pretty useless for examining similarities with words which can be written in either syllabary (Michael's ramen example earlier in the thread); and when catching typos caused by erroneous conversion from phonetic input to characters - e.g. intending to input 成長 (seichou, growth) but accidentally selecting 清聴 (seichou, courteous attention). Howver in this particular use case, as long as it doesn't produce false positives (I haven't looked at the patch) I don't think it would cause any problems (of the kind which would require actively excluding certain languages/character sets), it just wouldn't be quite as useful. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doing better at HINTing an appropriate column within errorMissingColumn()
On 14/06/17 11:57, Peter Geoghegan wrote: On Mon, Jun 16, 2014 at 7:09 PM, Ian Barwick i...@2ndquadrant.com wrote: Howver in this particular use case, as long as it doesn't produce false positives (I haven't looked at the patch) I don't think it would cause any problems (of the kind which would require actively excluding certain languages/character sets), it just wouldn't be quite as useful. I'm not sure what you mean by false positives. The patch just shows a HINT, where before there was none. It's possible for any number of reasons that it isn't the most useful possible suggestion, since Levenshtein distance is used as opposed to any other scheme that might be better sometimes. I think that the hint given is a generally useful piece of information in the event of an ERRCODE_UNDEFINED_COLUMN error. Obviously I think the patch is worthwhile, but fundamentally the HINT given is just a guess, as with the existing HINTs. I mean, does it come up with a suggestion in every case, even if there is no remotely similar column? E.g. would SELECT foo FROM some_table bring up column bar as a suggestion if bar is the only column in the table? Anyway, is there an up-to-date version of the patch available? The one from March doesn't seem to apply cleanly to HEAD. Thanks Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL support to define multi variables once
Hi On 14/06/13 16:20, Quan Zongliang wrote: Hi all, Please find the attachment. By my friend asking, for convenience, support to define multi variables in single PL/pgSQL line. Like this: CREATE OR REPLACE FUNCTION try_mutlivardef() RETURNS text AS $$ DECLARE local_a, local_b, local_c text := 'a1'; BEGIN return local_a || local_b || local_c; end; $$ LANGUAGE plpgsql; Please submit this patch to the current commitfest: https://commitfest.postgresql.org/action/commitfest_view?id=22 Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RETURNING PRIMARY KEY syntax extension
On 14/06/12 18:46, Jochem van Dieten wrote: On Wed, Jun 11, 2014 at 2:39 AM, Tom Lane wrote: I'm not even 100% sold that automatically returning the primary key is going to save any application logic. Could somebody point out *exactly* where an app is going to save effort with this type of syntax, compared to requesting the columns it wants by name? I haven't checked the code, but I am hoping it will help with the problem where a RETURNING * is added to a statement that is not an insert or update by the JDBC driver. That has been reported on the JDBC list at least twice, and the proposed workaround is neither very elegant nor very robust: https://groups.google.com/forum/#!msg/pgsql.interfaces.jdbc/7WY60JX3qyo/-v1fqDqLQKwJ Unfortunately that seems to be a JDBC-specific issue, which is outside of the scope of this particular patch (which proposes additional server-side syntax intended to make RETURNING * operations more efficient for certain use cases, but which is in itself not a JDBC change). Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RETURNING PRIMARY KEY syntax extension
On 14/06/12 20:58, Jochem van Dieten wrote: On Thu, Jun 12, 2014 at 12:25 PM, Ian Barwick wrote: On 14/06/12 18:46, Jochem van Dieten wrote: I haven't checked the code, but I am hoping it will help with the problem where a RETURNING * is added to a statement that is not an insert or update by the JDBC driver. That has been reported on the JDBC list at least twice, and the proposed workaround is neither very elegant nor very robust: https://groups.google.com/forum/#!msg/pgsql.interfaces.jdbc/7WY60JX3qyo/-v1fqDqLQKwJ Unfortunately that seems to be a JDBC-specific issue, which is outside of the scope of this particular patch (which proposes additional server-side syntax intended to make RETURNING * operations more efficient for certain use cases, but which is in itself not a JDBC change). But the obvious way to fix the JDBC issue is not to fix it by adding a 'mini parser' on the JDBC side, but to make SELECT ... RETURNING PRIMARY KEY a regular select that silently ignores the returning clause and doesn't throw an error on the server-side. That might still be outside the scope of this particular patch, but it would provide (additional) justification if it were supported. That would be adding superfluous, unused and unusable syntax of no potential value (there is no SELECT ... RETURNING and it wouldn't make any sense if there was) as a workaround for a driver issue - not going to happen. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RETURNING PRIMARY KEY syntax extension
On 09/06/14 14:47, David G Johnston wrote: Ian Barwick wrote Hi, The JDBC API provides the getGeneratedKeys() method as a way of retrieving primary key values without the need to explicitly specify the primary key column(s). This is a widely-used feature, however the implementation has significant performance drawbacks. Currently this feature is implemented in the JDBC driver by appending RETURNING * to the supplied statement. However this means all columns of affected rows will be returned to the client, which causes significant performance problems, particularly on wide tables. To mitigate this, it would be desirable to enable the JDBC driver to request only the primary key value(s). Seems like a good idea. ERROR: Relation does not have any primary key(s) Relation does not have a primary key. or Relation has no primary key. (preferred) By definition it cannot have more than one so it must have none. Ah yes, amazing what a fresh pair of eyes does :). The plural is the vestige of an earlier iteration which said something about the relation not having any primary key column(s). Will fix, thanks. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] RETURNING PRIMARY KEY syntax extension
Hi,
The JDBC API provides the getGeneratedKeys() method as a way of retrieving
primary key values without the need to explicitly specify the primary key
column(s). This is a widely-used feature, however the implementation has
significant
performance drawbacks.
Currently this feature is implemented in the JDBC driver by appending
RETURNING * to the supplied statement. However this means all columns of
affected rows will be returned to the client, which causes significant
performance problems, particularly on wide tables. To mitigate this, it would
be desirable to enable the JDBC driver to request only the primary key value(s).
One possible solution would be to have the driver request the primary key for
a table, but this could cause a race condition where the primary key could
change,
and even if it does not, it would entail extra overhead.
A more elegant and universal solution, which would allow the JDBC driver to
request the primary key in a single request, would be to extend the RETURNING
clause syntax with the option PRIMARY KEY. This resolves during parse
analysis into the columns of the primary key, which can be done unambiguously
because the table is already locked by that point and the primary key cannot
change.
A patch is attached which implements this, and will be added to the next
commitfest.
A separate patch will be submitted to the JDBC project. Example usage shown
below.
Regards
Ian Barwick
/* -- */
postgres=# CREATE TABLE foo (id SERIAL PRIMARY KEY);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(DEFAULT) RETURNING PRIMARY KEY;
id
1
(1 row)
INSERT 0 1
postgres=# CREATE TABLE bar (id1 INT NOT NULL, id2 INT NOT NULL, PRIMARY
KEY(id1, id2));
CREATE TABLE
postgres=# INSERT INTO bar VALUES(1,2) RETURNING PRIMARY KEY;
id1 | id2
-+-
1 | 2
(1 row)
INSERT 0 1
postgres=# INSERT INTO bar VALUES(2,1),(2,2) RETURNING PRIMARY KEY;
id1 | id2
-+-
2 | 1
2 | 2
(2 rows)
INSERT 0 2
postgres=# CREATE TABLE no_pkey (id SERIAL NOT NULL);
CREATE TABLE
postgres=# INSERT INTO no_pkey VALUES(DEFAULT) RETURNING id;
id
1
(1 row)
INSERT 0 1
postgres=# INSERT INTO no_pkey VALUES(DEFAULT) RETURNING PRIMARY KEY;
ERROR: Relation does not have any primary key(s)
/* -- */
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 74ea907..45295d1 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -25,7 +25,7 @@ PostgreSQL documentation
DELETE FROM [ ONLY ] replaceable class=PARAMETERtable_name/replaceable [ * ] [ [ AS ] replaceable class=parameteralias/replaceable ]
[ USING replaceable class=PARAMETERusing_list/replaceable ]
[ WHERE replaceable class=PARAMETERcondition/replaceable | WHERE CURRENT OF replaceable class=PARAMETERcursor_name/replaceable ]
-[ RETURNING * | replaceable class=parameteroutput_expression/replaceable [ [ AS ] replaceable class=parameteroutput_name/replaceable ] [, ...] ]
+[ RETURNING * | replaceable class=parameteroutput_expression/replaceable [ [ AS ] replaceable class=parameteroutput_name/replaceable ] [, ...] | PRIMARY KEY ]
/synopsis
/refsynopsisdiv
@@ -182,6 +182,17 @@ DELETE FROM [ ONLY ] replaceable class=PARAMETERtable_name/replaceable [ *
/para
/listitem
/varlistentry
+
+ varlistentry
+termliteralPRIMARY KEY/literal/term
+listitem
+ para
+ Returns the table's primary key column(s) after each row is deleted.
+ Cannot be combined with an replaceable class=PARAMETERoutput_expression/replaceable.
+ /para
+/listitem
+ /varlistentry
+
/variablelist
/refsect1
@@ -208,7 +219,9 @@ DELETE replaceable class=parametercount/replaceable
clause, the result will be similar to that of a commandSELECT/
statement containing the columns and values defined in the
literalRETURNING/ list, computed over the row(s) deleted by the
- command.
+ command. literalPRIMARY KEY/ can be specified to return the
+ primary key value(s) for each deleted row. An error will be raised
+ if the table does not have a primary key.
/para
/refsect1
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index a3cccb9..9fbd859 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -24,7 +24,7 @@ PostgreSQL documentation
[ WITH [ RECURSIVE ] replaceable class=parameterwith_query/replaceable [, ...] ]
INSERT INTO replaceable class=PARAMETERtable_name/replaceable [ ( replaceable class=PARAMETERcolumn_name/replaceable [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { replaceable class=PARAMETERexpression/replaceable | DEFAULT
[HACKERS] pgaudit - an auditing extension for PostgreSQL
use a ProcessUtility_hook to deal with other utility commands that are not handled by #1 and #2. For example, DROP on global objects in all versions and all non-DROP DDL for 9.3 or 9.4. 4. We use an ExecutorCheckPerms_hook to log SELECT and DML commands. 5. We use an object_access_hook and OAT_POST_CREATE/ALTER to handle CREATE/ALTER on relations in 9.3/9.4. We use OAT_FUNCTION_EXECUTE to log (non-catalog) function execution. Planned future improvements include: 1. Additional logging facilities, including to a separate audit log file and to syslog, and potentially logging to a table (possibly via a bgworker process). Currently output is simply emitted to the server log via ereport(). 2. To implement per-object auditing configuration, it would be nice to use extensible reloptions (or an equivalent mechanism) Details such as output format, command classification etc. are provisional and open to further discussion. Authors: Ian Barwick, Abhijit Menon-Sen (2ndQuadrant). See README.md for more details. We welcome your feedback and suggestions. Ian Barwick The research leading to these results has received funding from the European Union's Seventh Framework Programme (FP7/2007-2013) under grant agreement n° 318633. http://axleproject.eu -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD
On 24/04/14 09:26, Tatsuo Ishii wrote: Included is the graph (from PostgreSQL Enterprise Consortium's 2014 report page 13: https://www.pgecons.org/downloads/43). I see up to 14% degration (at 128 concurrent users) comparing with 9.2. That URL returns 'Forbidden'... Sorry for this. I sent a problem report to the person in charge. In the mean time, please go to: https://www.pgecons.org/download/works_2013/ then click the link 2013 年度WG1活動報告 (sorry for not English). You should be able to download a report (PDF). Also the report is written in Japanese. I hope you can read at leat the graph in page 13 and the table in page 14. Is pgecons planning to do a translation of that at some point? It looks like good material, and the audience able to understand it is rather limited now :) Yeah, once I proposed a translation of the documents by professional translators to the organization. Their decision was no. The main reason was cost. The document is huge and the translation work could cost tremendously. So unless someone comes up for volunteering the translation work, the document would not be translated. I actually started translating one of those reports on the way home from last year's PgCon (PgEcons made a presentation there: http://www.pgcon.org/2013/schedule/events/556.en.html ) - it was a long flight - but didn't have any particular incentive to finish it. It might make a nice JPUG project for members who want to practise their English. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: add psql tab completion for event triggers
On 10/04/14 00:23, Robert Haas wrote:
On Tue, Apr 8, 2014 at 5:27 AM, Ian Barwick i...@2ndquadrant.com wrote:
On 08/04/14 18:22, Ian Barwick wrote:
As it was kind of annoying not to have this when playing around with
event triggers.
This also tightens up the existing tab completion for ALTER TRIGGER,
which contained redundant code for table name completion, and which was
also causing a spurious RENAME TO to be inserted in this context:
CREATE EVENT TRIGGER foo ON {event} ^I
Apologies, previous patch had some unrelated changes in it.
Correct patch attached.
This *still* has some unrelated things in it, like s/Pgsql/Postgres/,
and numerous hunks consisting entirely of whitespace changes and/or
changes to unrelated comments.
Apologies again, that was ill-thought out. Revised patch attached with
only the additions related to event triggers, and the small fix for
ALTER TRIGGER mentioned above which ensures RENAME TO is applied only
when ALTER TRIGGER name ON sth was input; currently there is no
check for a preceding ALTER, resulting in the spurious RENAME TO
when completing CREATE EVENT TRIGGER.
Also, what's the point of this hunk:
*** psql_completion(const char *text, int st
*** 1318,1340
pg_strcasecmp(prev2_wd, TRIGGER) == 0)
COMPLETE_WITH_CONST(ON);
- else if (pg_strcasecmp(prev4_wd, ALTER) == 0
-pg_strcasecmp(prev3_wd, TRIGGER) == 0)
- {
- completion_info_charp = prev2_wd;
- COMPLETE_WITH_QUERY(Query_for_list_of_tables_for_trigger);
- }
-
/*
!* If we have ALTER TRIGGER sth ON, then add the correct tablename
*/
else if (pg_strcasecmp(prev4_wd, ALTER) == 0
pg_strcasecmp(prev3_wd, TRIGGER) == 0
pg_strcasecmp(prev_wd, ON) == 0)
! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
/* ALTER TRIGGER name ON name */
! else if (pg_strcasecmp(prev4_wd, TRIGGER) == 0
pg_strcasecmp(prev2_wd, ON) == 0)
COMPLETE_WITH_CONST(RENAME TO);
--- 1355,1374
pg_strcasecmp(prev2_wd, TRIGGER) == 0)
COMPLETE_WITH_CONST(ON);
/*
!* If we have ALTER TRIGGER name ON, then add the correct tablename
*/
else if (pg_strcasecmp(prev4_wd, ALTER) == 0
pg_strcasecmp(prev3_wd, TRIGGER) == 0
pg_strcasecmp(prev_wd, ON) == 0)
! {
! completion_info_charp = prev2_wd;
! COMPLETE_WITH_QUERY(Query_for_list_of_tables_for_trigger);
! }
/* ALTER TRIGGER name ON name */
! else if (pg_strcasecmp(prev5_wd, ALTER) == 0
!pg_strcasecmp(prev4_wd, TRIGGER) == 0
pg_strcasecmp(prev2_wd, ON) == 0)
COMPLETE_WITH_CONST(RENAME TO);
I'll submit that as a separate patch. This was intended to fix this:
else if (pg_strcasecmp(prev4_wd, ALTER) == 0
pg_strcasecmp(prev3_wd, TRIGGER) == 0)
{
completion_info_charp = prev2_wd;
COMPLETE_WITH_QUERY(Query_for_list_of_tables_for_trigger);
}
/*
* If we have ALTER TRIGGER sth ON, then add the correct tablename
*/
else if (pg_strcasecmp(prev4_wd, ALTER) == 0
pg_strcasecmp(prev3_wd, TRIGGER) == 0
pg_strcasecmp(prev_wd, ON) == 0)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
as the second else if clause is redundant.
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
new file mode 100644
index 202ffce..6d26ffc
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
*** static const SchemaQuery Query_for_list_
*** 714,719
--- 714,724
FROM pg_catalog.pg_prepared_statements \
WHERE substring(pg_catalog.quote_ident(name),1,%d)='%s'
+ #define Query_for_list_of_event_triggers \
+ SELECT pg_catalog.quote_ident(evtname) \
+FROM pg_catalog.pg_event_trigger \
+ WHERE substring(pg_catalog.quote_ident(evtname),1,%d)='%s'
+
/*
* This is a list of all things in Pgsql, which can show up after CREATE or
* DROP; and there is also a query to get a list of them.
*** static const pgsql_thing_t words_after_c
*** 746,751
--- 751,757
{DATABASE, Query_for_list_of_databases},
{DICTIONARY, Query_for_list_of_ts_dictionaries, NULL, THING_NO_SHOW},
{DOMAIN, NULL, Query_for_list_of_domains},
+ {EVENT TRIGGER, NULL, NULL},
{EXTENSION, Query_for_list_of_extensions},
{FOREIGN DATA WRAPPER, NULL, NULL},
{FOREIGN TABLE
[HACKERS] Doc typo in 9.28. Event Trigger Functions
Just a single missing 's'. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml new file mode 100644 index 6e2fbda..b5807f3 *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *** FOR EACH ROW EXECUTE PROCEDURE suppress_ *** 17446,17452 /para para ! functionpg_event_trigger_dropped_objects/ returns a list of all object dropped by the command in whose literalsql_drop/ event it is called. If called in any other context, functionpg_event_trigger_dropped_objects/ raises an error. --- 17446,17452 /para para ! functionpg_event_trigger_dropped_objects/ returns a list of all objects dropped by the command in whose literalsql_drop/ event it is called. If called in any other context, functionpg_event_trigger_dropped_objects/ raises an error. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch: add psql tab completion for event triggers
As it was kind of annoying not to have this when playing around with
event triggers.
This also tightens up the existing tab completion for ALTER TRIGGER,
which contained redundant code for table name completion, and which was
also causing a spurious RENAME TO to be inserted in this context:
CREATE EVENT TRIGGER foo ON {event} ^I
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 6e2fbda..b5807f3
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*** FOR EACH ROW EXECUTE PROCEDURE suppress_
*** 17446,17452
/para
para
! functionpg_event_trigger_dropped_objects/ returns a list of all object
dropped by the command in whose literalsql_drop/ event it is called.
If called in any other context,
functionpg_event_trigger_dropped_objects/ raises an error.
--- 17446,17452
/para
para
! functionpg_event_trigger_dropped_objects/ returns a list of all objects
dropped by the command in whose literalsql_drop/ event it is called.
If called in any other context,
functionpg_event_trigger_dropped_objects/ raises an error.
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
new file mode 100644
index 202ffce..7179642
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
*** static const SchemaQuery Query_for_list_
*** 714,721
FROM pg_catalog.pg_prepared_statements \
WHERE substring(pg_catalog.quote_ident(name),1,%d)='%s'
/*
! * This is a list of all things in Pgsql, which can show up after CREATE or
* DROP; and there is also a query to get a list of them.
*/
--- 714,726
FROM pg_catalog.pg_prepared_statements \
WHERE substring(pg_catalog.quote_ident(name),1,%d)='%s'
+ #define Query_for_list_of_event_triggers \
+ SELECT pg_catalog.quote_ident(evtname) \
+FROM pg_catalog.pg_event_trigger \
+ WHERE substring(pg_catalog.quote_ident(evtname),1,%d)='%s'
+
/*
! * This is a list of all things in Postgres, which can show up after CREATE or
* DROP; and there is also a query to get a list of them.
*/
*** static const pgsql_thing_t words_after_c
*** 746,751
--- 751,757
{DATABASE, Query_for_list_of_databases},
{DICTIONARY, Query_for_list_of_ts_dictionaries, NULL, THING_NO_SHOW},
{DOMAIN, NULL, Query_for_list_of_domains},
+ {EVENT TRIGGER, NULL, NULL},
{EXTENSION, Query_for_list_of_extensions},
{FOREIGN DATA WRAPPER, NULL, NULL},
{FOREIGN TABLE, NULL, NULL},
*** psql_completion(const char *text, int st
*** 934,942
{
static const char *const list_ALTER[] =
{AGGREGATE, COLLATION, CONVERSION, DATABASE, DEFAULT PRIVILEGES, DOMAIN,
! EXTENSION, FOREIGN DATA WRAPPER, FOREIGN TABLE, FUNCTION,
GROUP, INDEX, LANGUAGE, LARGE OBJECT, MATERIALIZED VIEW, OPERATOR,
! ROLE, RULE, SCHEMA, SERVER, SEQUENCE, SYSTEM SET, TABLE,
TABLESPACE, TEXT SEARCH, TRIGGER, TYPE,
USER, USER MAPPING FOR, VIEW, NULL};
--- 940,948
{
static const char *const list_ALTER[] =
{AGGREGATE, COLLATION, CONVERSION, DATABASE, DEFAULT PRIVILEGES, DOMAIN,
! EXTENSION, EVENT TRIGGER, FOREIGN DATA WRAPPER, FOREIGN TABLE, FUNCTION,
GROUP, INDEX, LANGUAGE, LARGE OBJECT, MATERIALIZED VIEW, OPERATOR,
! ROLE, RULE, SCHEMA, SERVER, SEQUENCE, SYSTEM SET, TABLE,
TABLESPACE, TEXT SEARCH, TRIGGER, TYPE,
USER, USER MAPPING FOR, VIEW, NULL};
*** psql_completion(const char *text, int st
*** 1013,1018
--- 1019,1055
COMPLETE_WITH_LIST(list_ALTEREXTENSION);
}
+ /* ALTER EVENT TRIGGER */
+ else if (pg_strcasecmp(prev3_wd, ALTER) == 0
+ pg_strcasecmp(prev2_wd, EVENT) == 0
+ pg_strcasecmp(prev_wd, TRIGGER) == 0)
+ {
+ COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
+ }
+
+ /* ALTER EVENT TRIGGER name */
+ else if (pg_strcasecmp(prev4_wd, ALTER) == 0
+ pg_strcasecmp(prev3_wd, EVENT) == 0
+ pg_strcasecmp(prev2_wd, TRIGGER) == 0)
+ {
+ static const char *const list_ALTER_EVENT_TRIGGER[] =
+ {DISABLE, ENABLE, OWNER TO, RENAME TO, NULL};
+
+ COMPLETE_WITH_LIST(list_ALTER_EVENT_TRIGGER);
+ }
+
+ /* ALTER EVENT TRIGGER name ENABLE */
+ else if (pg_strcasecmp(prev5_wd, ALTER) == 0
+ pg_strcasecmp(prev4_wd, EVENT) == 0
+ pg_strcasecmp(prev3_wd, TRIGGER) == 0
+ pg_strcasecmp(prev_wd, ENABLE) == 0)
+ {
+ static const char *const list_ALTER_EVENT_TRIGGER_ENABLE[] =
+ {REPLICA, ALWAYS, NULL};
+
+ COMPLETE_WITH_LIST(list_ALTER_EVENT_TRIGGER_ENABLE);
+ }
+
/* ALTER FOREIGN */
else if (pg_strcasecmp(prev2_wd, ALTER) == 0
pg_strcasecmp(prev_wd, FOREIGN) == 0)
*** psql_completion(const char *text, int st
*** 1318,1340
pg_strcasecmp(prev2_wd, TRIGGER) == 0
Re: [HACKERS] Patch: add psql tab completion for event triggers
On 08/04/14 18:22, Ian Barwick wrote:
As it was kind of annoying not to have this when playing around with
event triggers.
This also tightens up the existing tab completion for ALTER TRIGGER,
which contained redundant code for table name completion, and which was
also causing a spurious RENAME TO to be inserted in this context:
CREATE EVENT TRIGGER foo ON {event} ^I
Apologies, previous patch had some unrelated changes in it.
Correct patch attached.
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
new file mode 100644
index 202ffce..7179642
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
*** static const SchemaQuery Query_for_list_
*** 714,721
FROM pg_catalog.pg_prepared_statements \
WHERE substring(pg_catalog.quote_ident(name),1,%d)='%s'
/*
! * This is a list of all things in Pgsql, which can show up after CREATE or
* DROP; and there is also a query to get a list of them.
*/
--- 714,726
FROM pg_catalog.pg_prepared_statements \
WHERE substring(pg_catalog.quote_ident(name),1,%d)='%s'
+ #define Query_for_list_of_event_triggers \
+ SELECT pg_catalog.quote_ident(evtname) \
+FROM pg_catalog.pg_event_trigger \
+ WHERE substring(pg_catalog.quote_ident(evtname),1,%d)='%s'
+
/*
! * This is a list of all things in Postgres, which can show up after CREATE or
* DROP; and there is also a query to get a list of them.
*/
*** static const pgsql_thing_t words_after_c
*** 746,751
--- 751,757
{DATABASE, Query_for_list_of_databases},
{DICTIONARY, Query_for_list_of_ts_dictionaries, NULL, THING_NO_SHOW},
{DOMAIN, NULL, Query_for_list_of_domains},
+ {EVENT TRIGGER, NULL, NULL},
{EXTENSION, Query_for_list_of_extensions},
{FOREIGN DATA WRAPPER, NULL, NULL},
{FOREIGN TABLE, NULL, NULL},
*** psql_completion(const char *text, int st
*** 934,942
{
static const char *const list_ALTER[] =
{AGGREGATE, COLLATION, CONVERSION, DATABASE, DEFAULT PRIVILEGES, DOMAIN,
! EXTENSION, FOREIGN DATA WRAPPER, FOREIGN TABLE, FUNCTION,
GROUP, INDEX, LANGUAGE, LARGE OBJECT, MATERIALIZED VIEW, OPERATOR,
! ROLE, RULE, SCHEMA, SERVER, SEQUENCE, SYSTEM SET, TABLE,
TABLESPACE, TEXT SEARCH, TRIGGER, TYPE,
USER, USER MAPPING FOR, VIEW, NULL};
--- 940,948
{
static const char *const list_ALTER[] =
{AGGREGATE, COLLATION, CONVERSION, DATABASE, DEFAULT PRIVILEGES, DOMAIN,
! EXTENSION, EVENT TRIGGER, FOREIGN DATA WRAPPER, FOREIGN TABLE, FUNCTION,
GROUP, INDEX, LANGUAGE, LARGE OBJECT, MATERIALIZED VIEW, OPERATOR,
! ROLE, RULE, SCHEMA, SERVER, SEQUENCE, SYSTEM SET, TABLE,
TABLESPACE, TEXT SEARCH, TRIGGER, TYPE,
USER, USER MAPPING FOR, VIEW, NULL};
*** psql_completion(const char *text, int st
*** 1013,1018
--- 1019,1055
COMPLETE_WITH_LIST(list_ALTEREXTENSION);
}
+ /* ALTER EVENT TRIGGER */
+ else if (pg_strcasecmp(prev3_wd, ALTER) == 0
+ pg_strcasecmp(prev2_wd, EVENT) == 0
+ pg_strcasecmp(prev_wd, TRIGGER) == 0)
+ {
+ COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
+ }
+
+ /* ALTER EVENT TRIGGER name */
+ else if (pg_strcasecmp(prev4_wd, ALTER) == 0
+ pg_strcasecmp(prev3_wd, EVENT) == 0
+ pg_strcasecmp(prev2_wd, TRIGGER) == 0)
+ {
+ static const char *const list_ALTER_EVENT_TRIGGER[] =
+ {DISABLE, ENABLE, OWNER TO, RENAME TO, NULL};
+
+ COMPLETE_WITH_LIST(list_ALTER_EVENT_TRIGGER);
+ }
+
+ /* ALTER EVENT TRIGGER name ENABLE */
+ else if (pg_strcasecmp(prev5_wd, ALTER) == 0
+ pg_strcasecmp(prev4_wd, EVENT) == 0
+ pg_strcasecmp(prev3_wd, TRIGGER) == 0
+ pg_strcasecmp(prev_wd, ENABLE) == 0)
+ {
+ static const char *const list_ALTER_EVENT_TRIGGER_ENABLE[] =
+ {REPLICA, ALWAYS, NULL};
+
+ COMPLETE_WITH_LIST(list_ALTER_EVENT_TRIGGER_ENABLE);
+ }
+
/* ALTER FOREIGN */
else if (pg_strcasecmp(prev2_wd, ALTER) == 0
pg_strcasecmp(prev_wd, FOREIGN) == 0)
*** psql_completion(const char *text, int st
*** 1318,1340
pg_strcasecmp(prev2_wd, TRIGGER) == 0)
COMPLETE_WITH_CONST(ON);
- else if (pg_strcasecmp(prev4_wd, ALTER) == 0
- pg_strcasecmp(prev3_wd, TRIGGER) == 0)
- {
- completion_info_charp = prev2_wd;
- COMPLETE_WITH_QUERY(Query_for_list_of_tables_for_trigger);
- }
-
/*
! * If we have ALTER TRIGGER sth ON, then add the correct tablename
*/
else if (pg_strcasecmp(prev4_wd, ALTER) == 0
pg_strcasecmp(prev3_wd, TRIGGER) == 0
pg_strcasecmp(prev_wd, ON) == 0)
! COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
/* ALTER TRIGGER name ON name */
! else if (pg_strcasecmp(prev4_wd, TRIGGER) == 0
pg_strcasecmp(prev2_wd, ON) == 0)
COMPLETE_WITH_CONST(RENAME TO);
--- 1355,1374
Re: [HACKERS] Autonomous Transaction (WIP)
On 07/04/14 15:50, Craig Ringer wrote: On 04/07/2014 12:06 PM, Rajeev rastogi wrote: Syntax to create autonomous transaction can be as: */PRAGMA AUTONOMOUS TRANSACTION;/* Wouldn't you want to use SET TRANSACTION for this? Or a suffix on BEGIN, like BEGIN AUTONOMOUS TRANSACTION ? What's the logic behind introducing PRAGMA ? If you wanted to use that syntax for Oracle compatibility you'd need to use: PRAGMA AUTONOMOUS_TRANSACTION; (note underscore). FWIW the implementation in the patch uses PRAGMA AUTONOMOUS_TRANSACTION, the space is presumably a typo. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.0b1: ERROR: btree index keys must be ordered by attribute
2010/6/1 Bruce Momjian br...@momjian.us: Tom Lane wrote: (...) The index-based-max code is throwing in the IS NOT NULL condition without thought for where it has to go in the index condition order. Will look into fixing this tomorrow. FYI, this no longer throws an error in current CVS so was fixed by Tom. Thanks for the update, I can confirm the issue no longer occurs in beta2. Regards Ian Barwick -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 9.0b1: ERROR: btree index keys must be ordered by attribute
Hi I've just compiled the 9.0 beta1 source tarball and am testing my custom application against it (which has been running on PostgreSQL since 7.3 or so). The below statement results in the following error message: ERROR: btree index keys must be ordered by attribute evidently in relation to the subselect. The statement works fine on previous versions up to 8.4.3. I can provide more details later if required: SELECT o.object_id FROM object o INNER JOIN class c ON (o.class_id = c.class_id) INNER JOIN object_version ov ON (o.object_id = ov.object_id) INNER JOIN site ON (o.site_id=site.site_id) WHERE o.object_id = '3143' AND ov.version = '0' AND o.site_id = '2' AND ov.object_status_id = (SELECT MAX(ov1.object_status_id) FROM object_version ov1 WHERE o.object_id=ov1.object_id AND ov1.version = ov.version AND ov1.lang = ov.lang ) AND ov.lang = 'en' SELECT version(): PostgreSQL 9.0beta1 on i686-pc-linux-gnu, compiled by GCC gcc (Ubuntu 4.3.2-1ubuntu12) 4.3.2, 32-bit Ubuntu 8.10 running on a VIA C7-M Processor (netbook). Regards Ian Barwick -- 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] 9.0b1: ERROR: btree index keys must be ordered by attribute
2010/5/9 David Fetter da...@fetter.org: On Sun, May 09, 2010 at 05:48:27PM +0900, Ian Barwick wrote: Hi I've just compiled the 9.0 beta1 source tarball and am testing my custom application against it (which has been running on PostgreSQL since 7.3 or so). The below statement results in the following error message: ERROR: btree index keys must be ordered by attribute evidently in relation to the subselect. The statement works fine on previous versions up to 8.4.3. I can provide more details later if required: A self-contained way to reproduce this, ideally small, would be fantastic :) Unfortunately I'm a bit pressed for time right now :(, however in the meantime I have confirmed the same error crops up on OS X 10.5 and also with the query pruned to reference just one table: SELECT ov.object_id FROM object_version ov WHERE ov.object_id = '3143' AND ov.version = '0' AND ov.object_status_id = ( SELECT MAX(ov1.object_status_id) FROM object_version ov1 WHERE ov1.object_id=ov.object_id AND ov1.version = ov.version AND ov1.lang = ov.lang ) AND ov.lang = 'en'; PostgreSQL 9.0beta1 on i386-apple-darwin9.8.0, compiled by GCC i686-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465), 32-bit I will see if I can knock together a reproducible test case, might take a day or so. At the moment all I can report is that creating a reduced version of the object_version table in a fresh DB with no data does not reproduce the error when running the above query. Ian Barwick -- 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] 9.0b1: ERROR: btree index keys must be ordered by attribute
Hi 2010/5/10 Tom Lane t...@sss.pgh.pa.us: Ian Barwick barw...@gmail.com writes: 2010/5/9 David Fetter da...@fetter.org: A self-contained way to reproduce this, ideally small, would be fantastic :) s/fantastic/absolutely required to do anything with this report/ Yes, I appreciate that :) I am a bit pressed for time and as googling the error message didn't produce any kind of result I thought it better to at least give a heads-up on the offchance someone might be able to do something with it as is, and / or events overtake me and I never end up doing anything about it at all. Luckily this is easy to reproduce with a stripped-down version of the original table and minimal data set: CREATE TABLE object_version ( object_version_id SERIAL, object_id INT NOT NULL, version INT NOT NULL DEFAULT 0, object_status_idINT NOT NULL, parent_id INT DEFAULT NULL, owner_idINT NOT NULL, created TIMESTAMP(0) NOT NULL DEFAULT NOW(), langCHAR(2) NOT NULL, PRIMARY KEY (object_version_id), UNIQUE (object_id, version, object_status_id, lang) ); INSERT INTO object_version VALUES (DEFAULT, 1, 0, 0, NULL, 1, DEFAULT,'en'), (DEFAULT, 1, 0, -1, NULL, 1, DEFAULT,'en'), (DEFAULT, 1, 1, -1, NULL, 1, DEFAULT,'en'); SELECT ov.object_id FROM object_version ov WHERE ov.object_id = 1 AND ov.version ='0 AND ov.object_status_id = ( SELECT MAX(ov1.object_status_id) FROM object_version ov1 WHERE ov1.object_id=ov.object_id AND ov1.version = ov.version AND ov1.lang = ov.lang ) AND ov.lang = 'en'; SELECT version(); PostgreSQL 9.0beta1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Ubuntu 4.4.1-4ubuntu9) 4.4.1, 64-bit HTH Ian Barwick -- 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] 9.0b1: ERROR: btree index keys must be ordered by attribute
2010/5/10 Ian Barwick barw...@gmail.com: SELECT ov.object_id FROM object_version ov WHERE ov.object_id = 1 AND ov.version ='0 AND ov.object_status_id = ( SELECT MAX(ov1.object_status_id) FROM object_version ov1 WHERE ov1.object_id=ov.object_id AND ov1.version = ov.version AND ov1.lang = ov.lang ) AND ov.lang = 'en'; Apologies, slight cp error; correct version of query: SELECT ov.object_id FROM object_version ov WHERE ov.object_id = 1 AND ov.version =0 AND ov.object_status_id = ( SELECT MAX(ov1.object_status_id) FROM object_version ov1 WHERE ov1.object_id=ov.object_id AND ov1.version = ov.version AND ov1.lang = ov.lang ) AND ov.lang = 'en'; FWIW the test case works fine in 8.4.3 Ian Barwick -- 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] why table.name is translated to (name.*)::name?
2010/3/30 Pavel Stehule pavel.steh...@gmail.com: Hello, I was noticed on little bit strange feature of PostgreSQL 8.4 and 9.0 we can use a non existing column name. What does mean? postgres=# create table h(a int, b int); CREATE TABLE Time: 2,604 ms postgres=# insert into h values(199,22); INSERT 0 1 Time: 0,970 ms postgres=# explain verbose select h.name from h; QUERY PLAN - Seq Scan on public.h (cost=0.00..42.10 rows=2140 width=32) Output: (h.*)::name (2 rows) Time: 0,727 ms postgres=# select h.name from h; name -- (199,22) (1 row) Time: 0,589 ms postgres=# FYI this has caused me (and presumably a few other people) a bit of head-scratching, e.g.: http://archives.postgresql.org/pgsql-general/2010-03/msg00362.php I imagine it has some potential as a 'gotcha', as name is hardly an uncommon column name, but it's not an issue which can easily researched... Ian Barwick -- 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] 8.4b1: Query returning results in different order to 8.3
2009/4/19 Tom Lane t...@sss.pgh.pa.us Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Ian Barwick wrote: Note I'm not sure whether this is a bug, or whether the assumption made for the original query (that the row order returned by the subquery would be carried over to the main part of the query) is incorrect but just happened to work as expected pre-8.4. The latter. Without an ORDER BY (at the outermost level), the order of the result is not well defined. Before 8.4, UNION was always performed by a Sort + Unique, which explains why the output is always sorted in previous releases. 8.4 knows how to perform it with a Hash Aggregate, which doesn't yield sorted output. This is mentioned in the release notes, but I suppose we'd better promote it to the observe the following incompatibilities list... Thanks for clarifying that. The relevant section in the release notes (which I managed to miss) is this: http://www.postgresql.org/docs/8.4/static/release-8-4.html#AEN93685 It would certainly be worth an explicit mention as I imagine the previous behaviour has been consistent enough for queries to have come to rely on it. Regards Ian Barwick
Re: [HACKERS] perl namespace for postgres specific modules?
11 Aug 2006 15:57:42 -0700, Andrew Hammond [EMAIL PROTECTED]: I need to write a perl module which will parse a .pgpass file into a reasonable data-structure in memory. I may extend it later to go in the other direction (given a populated datastructure, write a .pgpass). The first question that came to mind is what namespace should I put this under? Is there any precedent for perl modules intended to support postgresql administration? If not, I suggest PostgreSQL::pgpass PostgreSQL sounds like a logical choice. However CPAN - which has product-specific namespaces such as DB2, MySQL, Msql and Oracle, has Postgres rather than PostgreSQL (though there are only two modules in it, one of which dates from 1998, see http://cpan.org/modules/by-module/Postgres/ ). Ian Barwick ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Duplicate rows sneaking in despite PRIMARY KEY / UNIQUE constraint
On 6/6/06, Tom Lane [EMAIL PROTECTED] wrote: Travis Cross [EMAIL PROTECTED] writes: I'm noticing that a handful (4-16) of rows with duplicate columns (uid,token) are sneaking into the table every day despite the primary key constraint. Corrupt index, looks like ... you might try reindexing the index. I don't believe that the PANIC you show has anything directly to do with duplicate entries. It is a symptom of corrupt index structure. Now a corrupt index might also explain failure to notice duplications, but changing your application isn't going to fix whatever is causing it. You need to look for server-side causes. Any database or system crashes on this server (before this problem started)? Do you *know* that the disk drive will not lie about write complete? What is the platform and storage system, anyway? FWIW I've seen similar behaviour to this (PostgreSQL processes exiting abnormally, index corruption with duplicate primary keys) on servers with defective RAM chips. Ian Barwick ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] psql: recall previous command?
On Tue, 22 Feb 2005 15:26:52 +1100, Neil Conway [EMAIL PROTECTED] wrote: Is there a way to recall the previous command in psql? Obviously, up arrow or Ctrl-P using readline and the default readline bindings is close, but it recalls the previous _line_ of input. That is not at all the same thing in the case of a multiple line command, for example. Just checking: do you mean something like the \e and \p commands? test= SELECT \timing Timing is off. test- 'Hello World'; ?column? - Hello World (1 row) test= \p SELECT 'Hello World'; test= Ian Barwick ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] CVS should die
On Fri, 5 Nov 2004 16:22:55 +0100, Peter Eisentraut [EMAIL PROTECTED] wrote: Am Freitag, 5. November 2004 14:13 schrieb Andrew Dunstan: I'll repeat an observation I made (more or less) last time we had this discussion: the loudest voice in it belongs to those who actually use the repository most. When Tom or Bruce or Peter (for example) tell us we need to change I'll take lots more notice. I'm certainly open to considering subversion, although I have a certain traumatic experience with it that may or may not be related to the BDB backend that it uses. Aha, glad I'm not the only one. Version 1.1 has a flat-file based backend which is not prone to BDB-permission-related problems, see: http://svnbook.red-bean.com/svnbook-1.1/ch05.html#svn-ch-5-sect-1.4 . It's only been around a few months though and the docs mention possible issues with scalability. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.0b4: COMMIT outside of a transaction echoes ROLLBACK
On Sat, 30 Oct 2004 16:45:22 -0400, Tom Lane [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Wed, Oct 27, 2004 at 09:29:21PM -0400, Tom Lane wrote: Wouldn't it be better to just stay in TBLOCK_STARTED state, as if the COMMIT were just some random utility command? It's the same thing, because CommitTransactionCommand acts identically either way. I changed it anyway because it seems simpler. Patch applied. Many thanks for this. I appreciate it's a fairly trivial issue, but seeing the word ROLLBACK when a commit, or at least a non-operation were expected, can do nasty things to one's blood pressure. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 8.0b4: COMMIT outside of a transaction echoes ROLLBACK
On Tue, 26 Oct 2004 21:42:19 -0400 (EDT), Bruce Momjian [EMAIL PROTECTED] wrote: Ian Barwick wrote: just wondering: test= select version(); version -- PostgreSQL 8.0.0beta4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux) (1 row) test= begin; BEGIN test= commit; COMMIT test= commit; WARNING: there is no transaction in progress ROLLBACK Is there any reason ROLLBACK and not COMMIT is echoed here? Because the transaction was not committed, but rather rolled back. Aha. It had me a little confused because between the first COMMIT and the second there were several screens of data, and I wasn't sure if I'd issued the first COMMIT. Seeing ROLLBACK made me unsure whether I was still in a transaction which had in just been rolled back. Pre 8.0 versions echo COMMIT in this situation. Thanks Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] 8.0b4: COMMIT outside of a transaction echoes ROLLBACK
just wondering: test= select version(); version -- PostgreSQL 8.0.0beta4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux) (1 row) test= begin; BEGIN test= commit; COMMIT test= commit; WARNING: there is no transaction in progress ROLLBACK Is there any reason ROLLBACK and not COMMIT is echoed here? Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Unexpected subquery behaviour
Apologies if this has been covered previously. Given a statement like this: SELECT * FROM foo WHERE id IN (SELECT id FROM bar) I would expect it to fail if bar does not have a column id. The test case below (tested in 7.4.3 and 7.4.1) shows this statement will however appear succeed, but produce a cartesian join (?) if bar contains a foreign key referencing foo.id. test= SELECT version(); version - PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux) (1 row) test= CREATE TABLE foo (id INT PRIMARY KEY); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for table foo CREATE TABLE test= CREATE TABLE bar (bar_id INT, foo_id INT REFERENCES foo(id)); CREATE TABLE test= INSERT into foo values(1); INSERT 7493530 1 test= INSERT into foo values(2); INSERT 7493531 1 test= INSERT into bar values(2,1); INSERT 7493532 1 test= SELECT * FROM foo WHERE id IN (SELECT id FROM bar); id 1 2 (2 rows) test= EXPLAIN SELECT * FROM foo WHERE id IN (SELECT id FROM bar); QUERY PLAN --- Seq Scan on foo (cost=0.00..2.04 rows=1 width=4) Filter: (subplan) SubPlan - Seq Scan on bar (cost=0.00..1.01 rows=1 width=0) (4 rows) test= SELECT id FROM bar; ERROR: column id does not exist test= SELECT * FROM foo WHERE id IN (SELECT bar.id FROM bar); ERROR: column bar.id does not exist test= ALTER TABLE bar RENAME foo_id TO id; ALTER TABLE test= SELECT * FROM foo WHERE id IN (SELECT id FROM bar); id 1 (1 row) Is this known behaviour, and is there a rationale behind it? Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Unexpected subquery behaviour
On Tue, 27 Jul 2004 01:33:44 +0200, Andreas Joseph Krogh [EMAIL PROTECTED] wrote: On Tuesday 27 July 2004 01:15, Ian Barwick wrote: Apologies if this has been covered previously. Given a statement like this: SELECT * FROM foo WHERE id IN (SELECT id FROM bar) I would expect it to fail if bar does not have a column id. The test case below (tested in 7.4.3 and 7.4.1) shows this statement will however appear succeed, but produce a cartesian join (?) if bar contains a foreign key referencing foo.id. [snip] test= SELECT * FROM foo WHERE id IN (SELECT id FROM bar); id 1 2 (2 rows) This, however, does not work: andreak=# SELECT * FROM foo WHERE id IN (SELECT b.id FROM bar b); ERROR: column b.id does not exist yes, I had that further down in the original example: test= SELECT * FROM foo WHERE id IN (SELECT bar.id FROM bar); ERROR: column bar.id does not exist Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Unexpected subquery behaviour
On Mon, 26 Jul 2004 16:32:33 -0700 (PDT), Stephan Szabo [EMAIL PROTECTED] wrote: On Tue, 27 Jul 2004, Ian Barwick wrote: Apologies if this has been covered previously. Given a statement like this: SELECT * FROM foo WHERE id IN (SELECT id FROM bar) I would expect it to fail if bar does not have a column id. The test case below (tested in 7.4.3 and 7.4.1) shows this statement will however appear succeed, but produce a cartesian join (?) if bar contains a foreign key referencing foo.id. The foreign key is not relevant, I just realized. Unfortunately, as far as we can tell, the spec allows subselects to contain references to outer columns and that those can be done without explicitly referencing the outer table. As such, the above is effectively equivalent to SELECT * FROM foo WHERE foo.id IN (SELECT foo.id FROM bar) in the case where foo has an id column and bar does not. Aha, interesting to know, though it looks somewhat odd. The reason I came up with this is because I was referencing the wrong column, which happened to exist in the outer table, which was producing unexpected results. Thanks Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PostgreSQL Backup problems with tsearch2
On Monday 10 November 2003 20:47, Ed Baer wrote: To whom it may concern, Please accept my apology if this is not the correct forum, I am new and was unable to find the correct location to send this question. If you don't wish to answer, could you please direct me to the correct place to ask the question. Try the OpenFTS-General list: http://sourceforge.net/mailarchive/forum.php?forum=openfts-general The tsearch2 homepage is here: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ (...) The question is: Is the problem with backing up and restoring a database which has tsearch2 installed and enabled delt with in Version 7.4 of PostgreSQL? If it's the problem with restoring the tsearch2-related functions, then no, and I'm not sure whether it's fixable (in the sense that a tsearch2 enabled database will do a painless dump/restore). I've had some success by making sure all tsearch2-related functions are in their own schema, which I don't dump or use for restoring; before restoring I recreate the schema from a script, then reload the other schemas. There's a slight gotcha though which I can't recall offhand. I'll try and write it up next time I got through the process. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [BUGS] Autocomplete TAB on Postgres7.4beta5 not working?
On Tuesday 28 October 2003 23:47, Tom Lane wrote: Alvaro Herrera Munoz [EMAIL PROTECTED] writes: I found it very irritating at first, but when I discovered that I could tab my way to syscatalogs by using pg_catalog. as prefix, I started feeling it was actually a nice behavior. Hm. Okay, Ian isn't completely alone then ;-) I tried out that approach just now, though, and found that I still had to type at least pg_c before I could get any tab completion help at all. Another odd thing was that after completing pg_catalog., it wouldn't go any further --- one must type p here, even though all the possible completions begin pg_. (Possibly that could be fixed, but I don't know readline's behavior well enough to be sure.) I'm not sure whether it's intended or not, but explicitly adding pg_catalog to the search path alleviates this. So that's five typed characters and two tabs before one starts getting into the system catalogs. That seems like a lot of typing. If Ian were willing to type one more character of his p-something table names before hitting tab (I assume he's not calling them pg-something), Err ;-) db= \d p page_template_cache pg_ts_cfg_pkey page_template_cache_ov_idxpg_ts_cfgmap page_template_cache_pkey pg_ts_cfgmap_pkey page_template_cache_template_idx pg_ts_dict pg_catalog. pg_ts_dict_pkey pg_temp_1.pg_ts_parser pg_toast. pg_ts_parser_pkey pg_ts_cfg public. (The pg_ts_% are all from tsearch2 BTW. Though, they`re all in a schema of their own so explicitly addressing them would work equally well too). then he'd not have a problem with the availability of tab completion for system catalogs. I'm sure I can live with that (one more character), at least until I finish the brainwave input extension ;-). Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [BUGS] Autocomplete TAB on Postgres7.4beta5 not working?
On Tuesday 28 October 2003 22:48, Tom Lane wrote: AFAICT there was no discussion about this issue when the patch was proposed and applied. But now that the point is raised I have to say that I don't like this change. I don't think system catalogs should be excluded from tab completion. They never were before 7.4, and I have not seen anyone complaining about that, other than Ian. Comments anyone? Guilty as charged? ;-) Just to clarify, the patch enables tab completion for catalog relations as long as the schema name pg_catalog is prepended. E.g. \d pg_c[tab].[tab] will get expansion for everything in pg_catalog. ISTR I found it very irritating that without this restriction \d p[tab] produces a very large number of selections for normal database work. Looking at a current database, I have about a dozen of relations beginning with p in the search path which I access with tab expansion frequently and I'm sure it would annoy me intensely if I got all the system tables as well every time. Mind you that's only my personal preference, I thought it might be unpopular but as no one has commented since... I can submit a correction but not today or tomorrow. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [HACKERS] [pgsql-advocacy] Need concrete Why Postgres not MySQL bullet list
On Thursday 21 August 2003 21:30, Manfred Koizar wrote: On Thu, 21 Aug 2003 15:05:52 +0200, I wrote: Just wondering, what other databases has transactable DDLs? Firebird. Stop! I withdraw that statement. I must have mis-read some feature list :-( Tests with InterBase 6 showed that you can change metadata within a transaction, but when you ROLLBACK, metadata changes persist. Aha. I was just about to ask about that, because I was experimenting with a 1.5 beta version without success. Doesn't seem to work there (though as I have little experience and virtually no docs I might be missing something). Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] [pgsql-advocacy] Need concrete Why Postgres not MySQL bullet list
On Thursday 21 August 2003 11:15, Shridhar Daithankar wrote: On 21 Aug 2003 at 0:22, Ian Barwick wrote: * DDL - Data definition language (table creation statements etc.) in MySQL are not transaction based and cannot be rolled back. Just wondering, what other databases has transactable DDLs? oracle seems to have autonomous transactions which is arthogonal. DB2 8.1 seems to support transaction-capable DDL. At least, a rollback following a CREATE TABLE causes the table to disappear. Haven't gone into it in any depth. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SOLUTION: Insert a Euro symbol as UTF-8 from a latin1 charset.
On Friday 13 June 2003 17:28, Roland Glenn McIntosh wrote:
This is my solution / bug report / RFC cross-posted from [GENERAL]
regarding insertion of hexadecimal characters from the command line.
---
Okay. I have NO IDEA why this works. If someone could enlighten me as to
the math involved I'd appreciate it. First, a little background:
The Euro symbol is unicode value 0x20AC. UTF-8 encoding is a way of
representing most unicode characters in two bytes, and most latin
characters in one byte.
The only way I have found to insert a euro symbol into the database from
the command line psql client is this: INSERT INTO mytable
VALUES('\342\202\254');
I don't know why this works. In hex, those octal values are:
E2 82 AC
My apologies, I forgot to mention converting to UTF-8 in my original
reply.
Additionally, according to the psql online documentation and man page:
Anything contained in single quotes is furthermore subject to C-like
substitutions for \n (new line), \t (tab), \digits, \0digits, and \0xdigits
(the character with the given decimal, octal, or hexadecimal code).
Those digits *should* be interpreted as decimal digits, but they aren't.
The man page for psql is either incorrect, or the implementation is buggy.
The docs are easy to misunderstand if you are scanning them in a hurry.
This section is referring to substitutions in psql's own meta commands,
not SQL statements, e.g. this:
\echo '\0xe2\0x82\0xac'
will display the Euro sign (assuming your terminal can print it).
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SOLUTION: Insert a Euro symbol as UTF-8 from a latin1 charset.
On Friday 13 June 2003 17:28, Roland Glenn McIntosh wrote:
This is my solution / bug report / RFC cross-posted from [GENERAL]
regarding insertion of hexadecimal characters from the command line.
---
Okay. I have NO IDEA why this works. If someone could enlighten me as to
the math involved I'd appreciate it. First, a little background:
The Euro symbol is unicode value 0x20AC. UTF-8 encoding is a way of
representing most unicode characters in two bytes, and most latin
characters in one byte.
The only way I have found to insert a euro symbol into the database from
the command line psql client is this: INSERT INTO mytable
VALUES('\342\202\254');
I don't know why this works. In hex, those octal values are:
E2 82 AC
My apologies, I forgot to mention converting to UTF-8 in my original
reply.
Additionally, according to the psql online documentation and man page:
Anything contained in single quotes is furthermore subject to C-like
substitutions for \n (new line), \t (tab), \digits, \0digits, and \0xdigits
(the character with the given decimal, octal, or hexadecimal code).
Those digits *should* be interpreted as decimal digits, but they aren't.
The man page for psql is either incorrect, or the implementation is buggy.
The docs are easy to misunderstand if you are scanning them in a hurry.
This section is referring to substitutions in psql's own meta commands,
not SQL statements, e.g. this:
\echo '\0xe2\0x82\0xac'
will display the Euro sign (assuming your terminal can print it).
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Linking to current docs
On Sunday 23 February 2003 20:52, Dave Page wrote: -Original Message- From: Ian Barwick [mailto:[EMAIL PROTECTED] Would it be possible to modify the new docs to provide similar functionality? E.g. something like http://www.postgresql.org/docs/view.php?version=currentidoc=0file=sql- createtable.html Done. Thanks! :-) Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Linking to current docs
With the old idocs it was possible to create links to the current version of individual documentation pages without having to provide a Postgres version number, e.g.: http://www.PostgreSQL.org/idocs/index.php?kernel-resources.html which would remain constant over longer periods of time. Providing no major restructuring of the docs takes place, this is a useful low-maintenance way of linking to the docs without worrying too much about version changes and seems to be used in a variety of places. With the new documentation at http://www.postgresql.org/docs/ (which is BTW very nice) this approach doesn't appear to work :-(. Would it be possible to modify the new docs to provide similar functionality? E.g. something like http://www.postgresql.org/docs/view.php?version=currentidoc=0file=sql-createtable.html If neccessary I can provide time to do this (though I have no idea of what would be involved ;-). Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Q: pg_catalog views, OIDs and search_path
I'm preparing a patch to make more psql slash commands tab-completable (\di, \dv etc-) and have come across the following dilemma: - only relations visible in the current search path should be returned [*] - to determine visibilty via pg_catalog.pg_table_is_visible(), the relation's OID is necessary; - using (say) pg_catalog.pg_views to obtain view names seems to be the cleaner approach (making psql independent from the backend etc.) - views don't come with OIDs As is psql currently uses pg_catalog.pg_views to complete view names, meaning it will happily tab-complete (say) DROP VIEW with a view _not_ in the current search path. If executed the statement naturally produces the error 'ERROR: view ... does not exist'. Q: is there any likelihood of the pg_catalog views (pg_views, pg_tables, pg_indexes, pg_rules, possibly others I have missed) returning the relevant OID or (probably cleaner) the result of pg_table_is_visible() as a boolean? Otherwise the only workaround will be to ignore the catalog views and work with pg_class directly, which I will probably do, but it feels like a step backwards. [*] at least, this is how \d currently behaves and IMHO is intuitive. \d should of course operate on schema names too, to enable completion of relation names not in the search path; tentative patch will follow. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Todo claim: psql tab completion on schema names
I have been playing around with psql's tab-completion.c and have something approaching a sane solution for tab expansion of schema names. As this seems to fall under the todo item Allow psql to do table completion for SELECT * FROM schema_part and table completion for SELECT * FROM schema_name I would like if possible to claim this, at least for a week or so. If someone has a working solution already please let me know because I have got quite involved in the file (there seem to be one or two areas not directly related to schemas which could be improved as well) and I wouldn't want to duplicate efforts. In this respect couple of general questions: - is there any reason why tab expansion of slash commands is only implemented in a few cases? E.g. for \d but not \di ? . is there any requirement to maintain any kind of backwards compatibilty to pre 7.3 clients? - pg_catalog.pg_table_is_visible() works on non-table relations as well, right? Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Todo claim: psql tab completion on schema names
On Sunday 16 February 2003 01:10, Rod Taylor wrote: I've been debating a mechanism which could build tab completion tables based on the documentation for a while now -- and was going to give it a try next week. If it works, that file would essentially disappear. OK, will hold off and see what happens. In this respect couple of general questions: - is there any reason why tab expansion of slash commands is only implemented in a few cases? E.g. for \d but not \di ? . is there any requirement to maintain any kind of backwards compatibilty to pre 7.3 clients? Post 7.3 launch most of these cases were cleaned up, not to say some weren't missed. You might want to take a look at CVS-TIP sources. It's what I have... Feel free to send in patches for ones that have been missed. ...what I meant is that after entering \d, TAB will produce a list of tables, but \di does not produce a list of indexes, same for \dv etc. I see no particular reason why this is so and can provide patches if relevant. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [mail] Re: [HACKERS] Windows Build System
On Friday 31 January 2003 05:08, Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: And what about MySQL? What about it? Someone claimed in this thread that MySQL's Windows port requires Cygwin. Is that true or not? For reference, from the INSTALL-SOURCE file included in the MySQL sources which I have lying about [*]: [*] danged legacy applications ;-) --QUOTE START-- Windows Source Distribution --- You will need the following: * VC++ 6.0 compiler (updated with 4 or 5 SP and Pre-processor package) The Pre-processor package is necessary for the macro assembler. More details at: `http://msdn.microsoft.com/vstudio/sp/vs6sp5/faq.asp'. * The MySQL source distribution for Windows, which can be downloaded from `http://www.mysql.com/downloads/'. Building MySQL 1. Create a work directory (e.g., workdir). 2. Unpack the source distribution in the aforementioned directory. 3. Start the VC++ 6.0 compiler. 4. In the `File' menu, select `Open Workspace'. 5. Open the `mysql.dsw' workspace you find on the work directory. 6. From the `Build' menu, select the `Set Active Configuration' menu. 7. Click over the screen selecting `mysqld - Win32 Debug' and click OK. 8. Press `F7' to begin the build of the debug server, libs, and some client applications. 9. When the compilation finishes, copy the libs and the executables to a separate directory. 10. Compile the release versions that you want, in the same way. 11. Create the directory for the MySQL stuff: e.g., `c:\mysql' 12. From the workdir directory copy for the c:\mysql directory the following directories: * Data * Docs * Share 13. Create the directory `c:\mysql\bin' and copy all the servers and clients that you compiled previously. 14. If you want, also create the `lib' directory and copy the libs that you compiled previously. 15. Do a clean using Visual Studio. Set up and start the server in the same way as for the binary Windows distribution. *Note Windows prepare environment::. --QUOTE END-- Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] A modest proposal for a FAQ addition
On Sunday 12 January 2003 06:17, Bruce Momjian wrote: Tom Lane wrote: Q: Why do I get strange results with a CHAR(n) field? A. Don't use CHAR(n). VARCHAR(n) has the behavior you are probably expecting; on top of which it's more compact and usually faster. I suppose the above needs some fleshing out, but man am I getting tired of explaining about significant vs non-significant trailing blanks. OK, good point. I was mentioning CHAR() in the FAQ entry first, while it should have been mentioned later. I also added a specific mention of the trailing spaces issue. Patch attached. Err, from the patch: CHAR(n) stores trailing spaces, while VARCHAR(n) trims them. Surely this should read something like: CHAR(n) automatically pads strings with trailing blanks to the defined column length. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] A modest proposal for a FAQ addition
On Sunday 12 January 2003 17:55, Bruce Momjian wrote: Peter Eisentraut wrote: Bruce Momjian writes: OK, new text is: I think Tom specifically wanted the notion don't use CHAR(n), it has unusual behavior to appear prominently in the FAQ. The current text simply rehashes the documentation. I can't say don't use CHAR(n) because there are valid reasons to use it. I think what Tom is saying is always use VARCHAR(n) unless you know for sure CHAR(n) is what you want, because if you slept through that part of the SQL course CHAR(n) is not what you might think. How about something like: 4.14.1 Why do operations on CHAR(n) columns produce strange results? Data inserted into a CHAR(n) column will be automatically padded with blanks to the specified column length. This makes some operations, particularly comparisions, appear to return unexpected results. For example, if you insert the string 'hello' (5 characters) into a column defined as CHAR(8) it will become 'hello ' (8 characters) and simple comparisions with the original 'hello' will fail. Always define columns with VARCHAR(n) unless you have specific reasons for using CHAR(n). Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] psql and readline
On Wednesday 08 January 2003 07:55, Christopher Kings-Lynne wrote: Hi, Is there any way of making the 'up' arrow retrieve all of the last multiline query, instead of just the last line? It's really annoying working with large multiline queries at the moment... Not that I know of, but you can use \e to edit the query in your favourite editor. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] psql and readline
On Wednesday 08 January 2003 13:02, Alexander M. Pravking wrote: On Wed, Jan 08, 2003 at 10:53:51AM +0100, Ian Barwick wrote: On Wednesday 08 January 2003 07:55, Christopher Kings-Lynne wrote: Hi, Is there any way of making the 'up' arrow retrieve all of the last multiline query, instead of just the last line? It's really annoying working with large multiline queries at the moment... Not that I know of, but you can use \e to edit the query in your favourite editor. Sure. But \e puts \e into history, instead of the query itself :( Yes, but the query will remain in the psql query buffer until a different SQL query (i.e. not a slash command) is executed. Until then you can reedit and / or reexecute the query (with \g) as long as you like. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [INTERFACES] Patch for DBD::Pg pg_relcheck problem
(B(crossposting to hackers) (B (BOn Tuesday 10 December 2002 00:47, Tom Lane wrote: (B In the next protocol version update (hopefully 7.4) I would like to see (B the basic version string (eg, "7.3.1" or "7.4devel") delivered to the (B client automatically during connection startup and then available from a (B libpq inquiry function. This would eliminate the need to call version() (B explicitly and to know that you must skip "PostgreSQL " in its output. (B (BSomething along the lines of (B char *PQversion(const PGconn *conn) ? (B (B However, it will only help for clients/libraries that are willing to (B deal exclusively with 7.4-or-newer backends, so it will take a few (B releases to become really useful. (B (BSounds good to me. Is it on the todo-list? (Couldn't see it there). (B (BIan Barwick ([EMAIL PROTECTED] (B (B (B---(end of broadcast)--- (BTIP 5: Have you checked our extensive FAQ? (B (Bhttp://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [INTERFACES] Patch for DBD::Pg pg_relcheck problem
(no followup to [EMAIL PROTECTED], getting a little OT there) (B (BOn Tuesday 10 December 2002 16:54, Lee Kindness wrote: (B Ian Barwick writes: (B Something along the lines of (B char *PQversion(const PGconn *conn) ? (B (B Probably: (B (B int PQversion(const PGconn *conn) (B (B would be better, and easier to parse? For example the value returned (B for 7.3.1 would be 7003001; for 7.4 7004000; for 101.10.2 (B 101010002. This allows simple numerical tests... (B (BSounds logical - I was evidently thinking in Perl ;-). (B (BFor reference pg_dump currently parses the SELECT version() string (Binto an integer thus: (B (B7.2 70200 (B7.2.1 70201 (B7.3devel70300 (B7.3rc1 70300 (B7.3.1 70301 (B7.3.99 70399 (B7.399.399 110299 (B101.10.2 1011002 (B (B(and just for fun: (B"11i Enterprise Edition with Bells and Whistles " (Breturns -1 ;-) (B (Bwhich works with minor release numbers of 99 (Band below. (B (BIan Barwick ([EMAIL PROTECTED] (B (B (B---(end of broadcast)--- (BTIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Q: unknown expression type 108 ?
On Friday 06 December 2002 20:50, Tom Lane wrote: (B Ian Barwick [EMAIL PROTECTED] writes: (B ERROR: ExecEvalExpr: unknown expression type 108 (B (B This is fixed as of yesterday --- see thread on (I think) pgsql-general (B for the patch. (B (BMany thanks. Applied and appears to work :-) (B (BFor reference: (Bhttp://groups.google.com/groups?dq=hl=enlr=ie=UTF-8selm=2630.1039125066%40sss.pgh.pa.us (B (B (BIan Barwick ([EMAIL PROTECTED] (B (B (B (B---(end of broadcast)--- (BTIP 3: if posting/reading through Usenet, please send an appropriate (Bsubscribe-nomail command to [EMAIL PROTECTED] so that your (Bmessage can get through to the mailing list cleanly
[HACKERS] Q: unknown expression type 108 ?
Hi appended below is a simple database schema (which may not be a candidate for the next Nobel Prize for SQL Database Design, but represents enough of a production database to demonstrate the following problem). And that is: under 7.3 this statement: SELECT foo_id, thingy_name, bar_name FROM foo_view, bar WHERE bar_id=foo_bar_id produces the desired results. This however: SELECT foo_id, thingy_name, bar_name FROM foo_view INNER JOIN bar ON bar_id=foo_bar_id produces ERROR: ExecEvalExpr: unknown expression type 108 The latter statement does however work in 7.1.3 with no apparent problems. Question: what does unknown expression type 108 mean and why should it suddenly occur in 7.3? A bit of Googling reveals the same message occurs when using subselects in constraints, but that doesn't seem related to this case. Ian Barwick [EMAIL PROTECTED] -- sample DB for unknown expression type 108 error CREATE TABLE a_thingy ( a_id INT, a_firstname VARCHAR(64), a_lastname VARCHAR(64), PRIMARY KEY (a_id) ); CREATE TABLE b_thingy ( b_id INT, b_name VARCHAR(64), PRIMARY KEY (b_id) ); CREATE TABLE bar ( bar_id INT, bar_name varchar(64), PRIMARY KEY (bar_id) ); CREATE TABLE foo ( foo_id INT, foo_a_id INT REFERENCES a_thingy NULL, foo_b_id INT REFERENCES b_thingy NULL, foo_bar_id INT REFERENCES bar NOT NULL, PRIMARY KEY (foo_id), CHECK((foo_a_id IS NOT NULL AND foo_b_id IS NULL) OR (foo_b_id IS NOT NULL AND foo_a_id IS NULL)) ); CREATE VIEW foo_view AS SELECT *, CASE WHEN foo_a_id IS NOT NULL THEN (SELECT a_lastname || ', ' || a_firstname FROM a_thingy WHERE a_id=foo_a_id ) WHEN foo_b_id IS NOT NULL THEN (SELECT b_name FROM b_thingy WHERE b_id=foo_b_id ) END AS thingy_name FROM foo; INSERT INTO a_thingy VALUES (1, 'John', 'Doe'); INSERT INTO b_thingy VALUES (1, 'Megacorp'); INSERT INTO bar VALUES(1, 'squid'); INSERT INTO bar VALUES(2, 'octopus'); INSERT INTO foo VALUES (1,1,NULL,1); INSERT INTO foo VALUES (2,NULL,1,2); -- END ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Boolean casting in 7.3 - changed?
On Thursday 28 November 2002 00:18, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Ian Barwick writes: Casting integers to boolean (for example, 0::bool) is no longer allowed, use '0'::bool instead. This advice would probably only cause more confusion, because we are now moving into the direction that character strings are no longer acceptable as numeric data. Yes, phrased that way it's just misleading. OK, what I am trying to say is something like: If you are upgrading an application to PostgreSQL 7.3 and are having problems with boolean casts which look like 0::bool or 1::bool, which previously worked without any problem, (although not explicitly supported) you will need to rewrite them to use the values listed here: http://www.postgresql.org/idocs/index.php?datatype-boolean.html . Doing things like '0'::bool will also work but is not recommended. because that's a problem I came across but found no mention of, so I thought I would point it out for the benefit of anyone else who might encounter it ;-) For reference, the reason why I was casting integer-like literals to boolean in the first place is: - Perl application used to run on a combination of MySQL and Oracle; - Perl doesn't have a boolean data type, but the values 0 and 1 in scalar context do the job just as well; - MySQL happily accepts literals for boolean column types, e.g. INSERT INTO table_with_boolean_column (boolean_column) VALUES (0) - the same statement in PostgreSQL produced ERROR: Attribute 'boolean_column' is of type 'bool' but expression is of type 'int4' You will need to rewrite or cast the expression - so I did what it said and wrote 0::bool - and thought no further of it, until now when I began the upgrade. - being in a bit of a hurry I put tried '0'::bool and it worked... - having rtfm, obviously just '0' and no mucking about with casting is better anyway... Peter Eisentraut [EMAIL PROTECTED] wrote: Note that x 0 is also a perfectly good way to convert integers to booleans, and a more portable one at that. Ah, that is a useful tip. Thanks for the information Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Boolean casting in 7.3 - changed?
A quick question: in 7.3 the following no longer works: template1= select 0::bool; ERROR: Cannot cast type integer to boolean The statement must be rewritten as this: template1= select '0'::bool; bool -- f (1 row) Is there a reason for this? I ask because the former query works in 7.1.3 and 7.2.1, but I haven't seen any mention of a change in 7.3 (at least not in the release notes). Apologies if this has been discussed to death previously, but it might be worth mentioning somewhere as a gotcha. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Boolean casting in 7.3 - changed?
On Wednesday 27 November 2002 06:23, Tom Lane wrote: (B Ian Barwick [EMAIL PROTECTED] writes: (B in 7.3 the following no longer works: (Btemplate1= select 0::bool; (BERROR: Cannot cast type integer to boolean (B (B Note that both old and new versions reject (B select 0::int4::bool; (B (B I believe the behavioral change is a consequence of Rod Taylor's (B DOMAIN patch: it essentially eliminated the old parser_typecast_constant() (B routine in order to ensure that constraints associated with a domain (B would get applied in examples like "select 0::domaintypename". (B (B I wasn't totally happy with Rod's patch, for reasons that I couldn't put (B my finger on at the time, but perhaps my hindbrain understood that there (B would be noticeable behavioral changes. But be that as it may, the code (B is in there now and is unlikely to get reverted. There isn't any place (B in our docs that promises that you can coerce an integer-looking literal (B to bool --- and one could argue that allowing such is just opening the (B door for typos. (B (BThanks for the explanation. I'm not screaming for a reversion ;-), but (Bchanging behaviour which was implicitly valid in previous (Bversions is bound to cause a few people a little head scratching (Bwhen converting applications to 7.3 (I'm sure I can't be the only one). (B (BHow about a line in HISTORY under "Migration to version 7.3" along (Bthe lines of: (B (B"Casting integers to boolean (for example, 0::bool) is no longer allowed, (Buse '0'::bool instead". (B (B (BIan Barwick ([EMAIL PROTECTED] (B (B (B---(end of broadcast)--- (BTIP 5: Have you checked our extensive FAQ? (B (Bhttp://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Request for supported platforms
On Tuesday 29 October 2002 01:56, Bruce Momjian wrote: Sorry, Ian, here is the patch I applied. You can apply this to whatever version you are using and test Irix with that, rather than having to grab CVS. OK, I have carried out make check with the updated tests but got FAILED on the same four tests (abstime, tinterval, horology, join). The failures are subtly different (see here for new regression diff): http://home.akademie.de/~IBarwick/IRIX_65_1.regression.diffs Having looked at them again I see the following: - in horology the timestamp tests seem to have succeeded (presumably the previous failures were triggered by the change to winter time); - the tests which are still failing in abstime, tinterval and horology all refer to dates before 1970, where AFAICS they are all out by one hour; possibly this explanation?: Some systems using older time zone libraries fail to apply daylight-saving corrections to dates before 1970, causing pre-1970 PDT times to be displayed in PST instead. This will result in localized differences in the test results. (cf. http://www.postgresql.org/idocs/index.php?regress-evaluation.html ); - the join tests are failing slightly differently; I would suggest that this is because the ORDER BY is still not explicit enough, and for what ever reason under IRIX the undefined result row orderings are in a different order to every other platform... e.g. with this statement: SELECT '' AS xxx, J1_TBL.i, j, t, k FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i) ORDER BY i; xxx | i | j | t | k -+---+---+---+ | 0 | | zero | | 1 | 4 | one | -1 | 2 | 3 | two | 2 | 2 | 3 | two | 4 | 3 | 2 | three | -3 | 4 | 1 | four | | 5 | 0 | five | -5 | 5 | 0 | five | -5 | 6 | 6 | six | | 7 | 7 | seven | | 8 | 8 | eight | | | 0 | zero | | | | null | (13 rows) the order of the last two rows is not defined. The expected order according to the regression tests is: | | | null | | | 0 | zero | Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Request for supported platforms
On Tuesday 29 October 2002 00:08, Bruce Momjian wrote: Neil Conway wrote: Bruce Momjian [EMAIL PROTECTED] writes: Ports list updated: Shouldn't the join regression test failure be fixed? OK, I have updated the join regression test to add ORDER BY in the queries Irix differed. Ian, would you retest the CVS version of PostgreSQL to see if all the regression tests pass now? For confirmation: IIRC CVS now requires the latest Bison (1.75)? (The Irix machine has 1.35). Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
