Re: [BUGS] BUG #8467: Slightly confusing pgcrypto example in docs
On Thu, Oct 10, 2013 at 08:32:32PM -0400, Bruce Momjian wrote: > > > How can the later entry not be MD5 hash? > > > > Because what you pass to the functions is 'md5', not 'md5 hash', which > > is what the new text appears to indicate. > > So if we revert, will it still be clear what is MD5 and what is MD5 hash? I mean, will it be clear what is MD5 crypt and what is MD5 hash? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8467: Slightly confusing pgcrypto example in docs
On Thu, Oct 10, 2013 at 08:22:30PM -0400, Peter Eisentraut wrote: > On Thu, 2013-10-10 at 19:14 -0400, Bruce Momjian wrote: > > > The changes shown below are incorrect, I think. > > > > > > > > > On 10/2/13 12:00 PM, Bruce Momjian wrote: > > > > *** gen_salt(type text [, iter_count integer > > > > *** 353,359 > > > > 12 years > > > > > > > > > > > > !md5 > > > > 2345086 > > > > 1 day > > > > 3 years > > > > --- 358,364 > > > > 12 years > > > > > > > > > > > > !md5 hash > > > > Uh, the table already has a mention of md5 crypt above: > > > >crypt-md5 > > > > How can the later entry not be MD5 hash? > > Because what you pass to the functions is 'md5', not 'md5 hash', which > is what the new text appears to indicate. So if we revert, will it still be clear what is MD5 and what is MD5 hash? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8516: Calling VOLATILE from STABLE function
On Thu, Oct 10, 2013 at 04:10:35PM -0700, Dwayne Towell wrote: > > According to the documentation, f() should be marked VOLATILE also, since > > calling f() produces side effects. PostgreSQL does not give a warning (or > > better yet, an error); I think it should. > > I think the answer is that function authors are required to prevent > functions they mark as STABLE from calling VOLATILE functions. > > -- > > I understand it's an error (at least usually), my question/issue is why does > PostgreSQL NOT give at least a warning when a programmer (probably > accidentally) calls a VOLATILE function in one that he has specifically > tagged as STABLE? The compiler has all the information to notify the > programmer of a mistake, but isn't. This violates a fundamental principle of > software engineering--take every opportunity to prevent errors. Well, we can't walk the function tree to know all called functions, and those they call, so we don't even try. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8467: Slightly confusing pgcrypto example in docs
On Thu, Oct 10, 2013 at 04:05:50PM -0400, Peter Eisentraut wrote: > The changes shown below are incorrect, I think. > > > On 10/2/13 12:00 PM, Bruce Momjian wrote: > > *** gen_salt(type text [, iter_count integer > > *** 353,359 > > 12 years > > > > > > !md5 > > 2345086 > > 1 day > > 3 years > > --- 358,364 > > 12 years > > > > > > !md5 hash Uh, the table already has a mention of md5 crypt above: crypt-md5 How can the later entry not be MD5 hash? > > 2345086 > > 1 day > > 3 years > > *** gen_salt(type text [, iter_count integer > > *** 380,386 > > > > > > > > ! md5 numbers are from mdcrack 1.2. > > > > > > > > --- 385,391 > > > > > > > > ! md5 hash numbers are from mdcrack 1.2. > > > > > > > > *** gen_random_bytes(count integer) returns > > *** 1343,1349 > > OpenBSD sys/crypto > > > > > > ! MD5 and SHA1 > > WIDE Project > > KAME kame/sys/crypto > > > > --- 1348,1354 ---- > > OpenBSD sys/crypto > > > > > > ! MD5 hash and SHA1 > > WIDE Project > > KAME kame/sys/crypto > > > Again, "MD5 crypt" is mentioned in the same table above: MD5 crypt so how can this not be md5 hash? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8516: Calling VOLATILE from STABLE function
On Wed, Oct 9, 2013 at 08:58:46PM +, dwa...@docketnavigator.com wrote: > The following bug has been logged on the website: > > Bug reference: 8516 > Logged by: Dwayne Towell > Email address: dwa...@docketnavigator.com > PostgreSQL version: 9.2.4 > Operating system: CentOS > Description: > > Why doesn't PostgreSQL give a warning when calling a volatile function from > a stable function? > > > For example: > CREATE TABLE x (val double); > > > CREATE FUNCTION g() RETURNS boolean AS $$ > INSERT INTO x SELECT rand() RETURNING val>0.5; > $$ LANGUAGE SQL VOLATILE; > > > CREATE FUNCTION f() RETURNS boolean AS $$ > SELECT g(); -- this is where the stability-violation happens > $$ LANGUAGE SQL STABLE; -- this is a lie > > > According to the documentation, f() should be marked VOLATILE also, since > calling f() produces side effects. PostgreSQL does not give a warning (or > better yet, an error); I think it should. I think the answer is that function authors are required to prevent functions they mark as STABLE from calling VOLATILE functions. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8467: Slightly confusing pgcrypto example in docs
On Wed, Oct 2, 2013 at 12:00:44PM -0400, Bruce Momjian wrote: > Based on your report, I have developed the attached doc patch which > clarifies when MD5 hash is being referenced, and when MD5 crypt is. I > have also added your other suggestions. Patch applied, and backpatched to 9.3.X. Thanks for the suggestions. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Installing/Upgrading PostgreSQL 9.1.6 to 9.3 known bugs?
On Mon, Oct 7, 2013 at 08:07:42AM -0700, fburg...@radiantblue.com wrote: > Bruce, Proposed Steps. Do they look feasible? > > 1.) pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f "/somepath/ > testdb.backup" testdb > 2.) CREATE DATABASE newdb TEMPLATE=template_postgis; > 3.) perl ../postgis-postgres/postgis-2.1.1/utils/postgis_restore.pl > "/somepath/ > testdb.backup" | psql -h localhost -p 5432 -U postgres newdb 2> errors.txt <- > this step may run 5-6 days, since our backup runs that long, right? > 4.) At this point we will have two 6.1TB databases, so it looks like a > prerequisite is to have available double the db size in disk space, right? > 5.) then if no critical errors, there will be errors since we have our testdb > schema in the public folder > 5a.) ALTER DATABASE testdb RENAME TO olddb; > 5b.) ALTER DATABASE newdb RENAME TO testdb; > 6.) At this point hopefully we should be upgraded from postgis 1.5.3 to > postgis > 2.1.1, with PostgreSQL 9.1.6 > 7.) then can we just use pg_upgrade with the hard links option, instead of > copying files to the new cluster option to upgrade to PostgreSQL 9.3? Sorry, I have no idea how to upgrade PostGIS. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8469: Xpath behaviour unintuitive / arguably wrong
On Fri, Oct 4, 2013 at 10:20:46PM +0200, Stefan Kaltenbrunner wrote: > >> http://www.postgresql.org/message-id/201106291934.23089.rsmog...@softperience.eu > > > > There are two other similar bug reports on this from February and March > > of this year: > > > > > > http://www.postgresql.org/message-id/e1u1fkl-0002rd...@wrihigleys.postgresql.org > > I think that should be: > http://www.postgresql.org/message-id/e1u1fkl-0002rd...@wrigleys.postgresql.org > > > > > > http://www.postgresql.org/message-id/e1uhyuw-0001oj...@wrigleys.postgresql.org > > > > Someone who knows XML needs to take leadership on this and propose a > > patch. Added to TODO. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade 9.0->9.2 failure: Mismatch of relation OID in database
On Thu, Sep 26, 2013 at 02:59:30PM +0200, Christoph Berg wrote:
> On upgrading a 9.0 database to 9.2 using pg_upgrade, I got this:
>
> # pg_upgradecluster -m upgrade 9.0 main /psql/data-9.2
> [...]
> Performing Upgrade
> --
> [...]
> Restoring database schema to new clusterok
> Removing support functions from new cluster ok
> Copying user relation files
>
> Mismatch of relation OID in database "hisrm": old OID 18804, new OID 18803
> Failure, exiting
> Error: pg_upgrade run failed
>
>
> This is a cluster that was running with 9.0.12 (compiled locally). For
> the upgrade, I installed postgresql-9.0 and -9.2 from
> apt.postgresql.org (9.0.13, 9.2.4), so pg_upgrade was using these
> versions. OS is Ubuntu 12.04 amd64 now and was 8.04 while the cluster
> was still running on 9.0.12.
>
> In the 9.0 cluster, 18804 is the relation oid of glm_lrahm_to_se.
>
>
> pg_upgrade_dump_all.sql:
> --
> -- Name: glm_lrahm_to_se; Type: TABLE; Schema: mbs; Owner: fsv; Tablespace:
> --
>
>
> -- For binary upgrade, must preserve pg_type oid
> SELECT binary_upgrade.set_next_pg_type_oid('18806'::pg_catalog.oid);
>
>
> -- For binary upgrade, must preserve pg_type array oid
> SELECT binary_upgrade.set_next_array_pg_type_oid('18805'::pg_catalog.oid);
>
>
> -- For binary upgrade, must preserve pg_class oids
> SELECT binary_upgrade.set_next_heap_pg_class_oid('18804'::pg_catalog.oid);
>
> CREATE TABLE glm_lrahm_to_se (
> id integer NOT NULL,
> lrahm integer NOT NULL,
> se integer NOT NULL
> );
That is very interesting, and it certainly should not be failing.
I am surprised it got an oid that was one less than the desired one,
18803. Is there any mention of 18803 in the SQL file? If you create a
cluster with just your schema and no data, can you upgrade that cleanly?
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8469: Xpath behaviour unintuitive / arguably wrong
On Tue, Sep 24, 2013 at 06:43:19PM +, dennis.noord...@helsinki.fi wrote:
> The following bug has been logged on the website:
>
> Bug reference: 8469
> Logged by: Dennis
> Email address: dennis.noord...@helsinki.fi
> PostgreSQL version: 9.3.0
> Operating system: FreeBSD 9.2-RC4
> Description:
>
> Hi,
>
>
> After upgrading an 8.1 version to 9.3.0 I am suddenly seeing text fields
> containing "&" where they are populated from XML. This may be a
> coincidence and the problem may have existed earlier, in any case, now I
> noticed.
>
>
> I extract the text content of XML nodes using xpath, from something like:
>
>
> Jones & Smith
>
>
> The reason I end up with "&" is the IMHO rather odd xpath behaviour:
>
>
> # select xpath('/a/text()', (select xmlelement(name "a", 'A & B')));
>
>
> xpath
> ---
> {"A & B"}
>
>
> The canonical contents of "a" is "A & B". At first search I've found some
> rather heated debates about this with bits of name calling; I certainly do
> not want to get into that and I apologize in advance to those who feel very
> strongly about this.
>
>
> I've seen one "fix" describe the problem as:
>
>
> ""DESCRIPTION: Submitter invokes following statement:
> SELECT (XPATH('/*/text()', '<'))[1].
> He expect (escaped) result "<", but gets "<"
> """
>
>
> With respect, this "bug" makes no sense as this produces in fact the right
> result. The actual value of is "<", it's just escaped when serialized
> to XML. If were to actually contain "<", it'd be serialized as
> "<". It should not be possible to be blindly cast to a text type, but
> explicitly serialized as such.
>
>
> At least the reviewer at:
>
>
> http://www.postgresql.org/message-id/201106291934.23089.rsmog...@softperience.eu
There are two other similar bug reports on this from February and March
of this year:
http://www.postgresql.org/message-id/e1u1fkl-0002rd...@wrihigleys.postgresql.org
http://www.postgresql.org/message-id/e1uhyuw-0001oj...@wrigleys.postgresql.org
Someone who knows XML needs to take leadership on this and propose a
patch.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8467: Slightly confusing pgcrypto example in docs
On Tue, Sep 24, 2013 at 11:20:55PM +0100, Richard Neill wrote:
> I'm sorry about that: I think I need to correct my proposed
> correction! I think I've been writing too much C recently, and so I
> foolishly mis-read that as returning pswhash, rather than returning
> the truth of the comparison.
>
> What I meant to write, for clarity, was:
>
> SELECT (pswhash = crypt('entered password', pswhash)) AS pswmatch FROM ... ;
>
> which would make it obvious that we're returning the boolean named pswmatch.
>
> >
> >>[Also, should the default example perhaps use gen_salt('bf'), as opposed to
> >>gen_salt('md5') ?]
> >
> >This, however, might be a good idea. People should of course always
> >read the documentation, but having the examples including the "best
> >practice" would probably be a good idea.
>
> Incidentally, there are 2 other things that confused me in this section.
>
> 1. Table F-18. Supported algorithms for crypt() has a column
> labelled "max password length". It would perhaps also be useful to
> know the size of column needed to store the crypted password (my
> original crypt using md5 easily fits in a varchar(70), whereas using
> bf needs the column to be varchar(100).)
>
>
> 2. Table F-20. Hash algorithm speeds
>
> What's the difference here between "crypt-md5" and "md5" ?
>
> If I've rightly read this, the algorithm named "md5" in the crypt()
> documentation is named "crypt-md5" here, whereas Table F20's "md5"
> algorithm seems to refer to something else - probably the "normal"
> version of md5.
>
> If so, it would be clearer to write that the last 2 lines ("md5" and
> "sha1") are for comparison only, and refer to the speed of doing an
> ordinary md5/sha1 sum, rather than the md5-variant of crypt().
>
>
> Anyway, thanks again for your help - Postgres is a wonderful system,
> which I've found to be repeatedly useful.
Based on your report, I have developed the attached doc patch which
clarifies when MD5 hash is being referenced, and when MD5 crypt is. I
have also added your other suggestions.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/doc/src/sgml/pgcrypto.sgml b/doc/src/sgml/pgcrypto.sgml
new file mode 100644
index a0eead7..976c7db
*** a/doc/src/sgml/pgcrypto.sgml
--- b/doc/src/sgml/pgcrypto.sgml
*** hmac(data bytea, key text, type text) re
*** 95,102
!The algorithms in crypt() differ from usual hashing algorithms
!like MD5 or SHA1 in the following respects:
--- 95,102
!The algorithms in crypt() differ from the usual
!MD5 or SHA1 hashing algorithms in the following respects:
*** hmac(data bytea, key text, type text) re
*** 142,147
--- 142,148
Max Password Length
Adaptive?
Salt Bits
+ Output length
Description
*** hmac(data bytea, key text, type text) re
*** 151,156
--- 152,158
72
yes
128
+ 60
Blowfish-based, variant 2a
*** hmac(data bytea, key text, type text) re
*** 158,163
--- 160,166
unlimited
no
48
+ 34
MD5-based crypt
*** hmac(data bytea, key text, type text) re
*** 165,170
--- 168,174
8
yes
24
+ 20
Extended DES
*** hmac(data bytea, key text, type text) re
*** 172,177
--- 176,182
8
no
12
+ 13
Original UNIX crypt
*** UPDATE ... SET pswhash = crypt('new pass
*** 205,211
Example of authentication:
! SELECT pswhash = crypt('entered password', pswhash) FROM ... ;
This returns true if the entered password is correct.
--- 210,216
Example of authentication:
! SELECT (pswhash = crypt('entered password', pswhash)) AS pswmatch FROM ... ;
This returns true if the entered password is correct.
*** gen_salt(type text [, iter_count integer
*** 353,359
12 years
!md5
2345086
1 day
3 years
--- 358,364
12 years
!md5 hash
2345086
1 day
3 years
*** gen_salt(type text [, iter_count integer
*** 380,386
! md5 numbers are from mdcrack 1.2.
--- 385,391
Re: [BUGS] Installing/Upgrading PostgreSQL 9.1.6 to 9.3 known bugs?
On Fri, Sep 20, 2013 at 02:00:05PM -0700, John R Pierce wrote: > On 9/20/2013 1:51 PM, fburg...@radiantblue.com wrote: > > 1.) During our prior upgrade process we used pg_upgrade to move from pg > 8.4.3 to 9.1.6 using the hard links install option, we also have our data > spread across disk storage mediums; fiber, nas. Are there any known > issues, > bugs with using pg_upgrade to move from 9.1.6 to pg 9.3? > > > > having multiple tablespaces on different mount points complicates things. > I'm > not sure how or if pg_upgrade handles that. pg_upgrade is fine with that. The old/new tablespaces stay in the same mount point as just per-version subdirectories. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] psql \i command with a missing file name doesn't rollback the transaction
On Fri, Sep 20, 2013 at 11:15:01AM +0200, Alexey Klyukin wrote: > Hi, > > We've discovered a surprising behavior of psql \i command. What we sometimes > to > add new tables to the database is: > > begin; > \i /path/to/table/definitions/table1.sql > \i /path/to/table/definitions/table2.sql > ... > \i /path/to/table/definitions/tableN.sql > commit; > > What we discovered that some files in the /path/to/table/definitions were > missing (say, table 2,3), but the table 1, 4... N appeared in the database > after executing the transaction. This is quite a catch, since we cannot rely > on > transaction consistency when using an include directive. > > The test is simple: > > begin; > \i whatever; > select 1; > commit; > > The expected behavior was that select 1 would lead to 'ERROR: current > transaction is aborted'. > The current behavior is that it is executed, although a message is emitted to > a > client: > whatever: No such file or directory > > Would it be possible from the client side to generate the rollback to the > server on an attempt to include a non-existing file (perhaps only when > ON_ERROR_STOP is set to 1?). The problem is how would we decide what psql actions should trigger a rollback, and how would we show the user we did that. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8455: spanish pgadmin3.mo
On Mon, Sep 16, 2013 at 08:40:57AM +, j.rom...@salsa.es wrote: > The following bug has been logged on the website: > > Bug reference: 8455 > Logged by: Jesus Romero > Email address: j.rom...@salsa.es > PostgreSQL version: 9.1.9 > Operating system: Ubuntu server 12.04 > Description: > > The actual version of pgadmin3 1.18 includes a wrong file pgadmin3.mo for > the spanish languaje. The file included is catalan languaje not the spanish > one. You should report this to the pgadmin developers on one of their email lists: http://www.pgadmin.org/support/list.php -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7817: psql does not relate to footer settings in extended mode
Applied.
---
On Mon, Sep 9, 2013 at 09:16:05PM -0400, Bruce Momjian wrote:
> On Sun, Jan 20, 2013 at 10:33:37AM +, emes...@redhat.com wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 7817
> > Logged by: Eli Mesika
> > Email address: emes...@redhat.com
> > PostgreSQL version: 9.1.7
> > Operating system: Fedora 16
> > Description:
> >
> > psql does not relate to footer settings in extended mode
> > Sometimes we need to run a sql command withot generating header and footer.
> > This can be done using the -t flag and --pset=footer=off
> > The problem is that the footer is still diaplyed even if it was set to off
> > if we use the extended mode for the query (-x flag)
> >
> > Steps to Reproduce:
> > 1) create a table without any data
> > for example
> > create table text(i int);
> > 2) run
> > psql -U -t --pset=footer=off
> > 3) No output is generated
> > 4) run
> > psql -U -t --pset=footer=off -x
> > 5) Output generated : "(No Rows)"
> >
> > Actual results:
> > psql does not honour the footer settings when output is defined to be in
> > Extended Mode
> >
> > Expected results:
> > psql should not generate any output is query has no results and -t and
> > --pset=footer=off were given
>
> This has been fixed in PG 9.3 (released today) for the specific options
> you supplied:
>
> $ psql -t --pset=footer=off test
> Default footer is off.
> psql (9.4devel)
> Type "help" for help.
>
> CREATE TABLE test (x INT);
> CREATE TABLE
> SELECT * FROM test;
>
> \x
> Expanded display is on.
> SELECT * FROM test;
>
> Unfortunately, this did not fix the more simple case where
> --pset=footer=off is specified, but not -t:
>
> $ psql --pset=footer=off test
> Default footer is off.
> psql (9.4devel)
> Type "help" for help.
>
> CREATE TABLE test (x INT);
> CREATE TABLE
> SELECT * FROM test;
>x
> ---
>
> \x
> Expanded display is on.
> SELECT * FROM test;
> --> (No rows)
>
> The attached patch fixes this, and makes it match the rest of the output
> formats, which do honor --pset=footer=off alone for footers.
>
> --
> Bruce Momjian http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
> diff --git a/src/bin/psql/print.c b/src/bin/psql/print.c
> new file mode 100644
> index 5589cea..736225c
> *** a/src/bin/psql/print.c
> --- b/src/bin/psql/print.c
> *** print_aligned_vertical(const printTableC
> *** 1171,1177
> if (cont->cells[0] == NULL && cont->opt->start_table &&
> cont->opt->stop_table)
> {
> ! if (!opt_tuples_only)
> fprintf(fout, _("(No rows)\n"));
> return;
> }
> --- 1171,1177 ----
> if (cont->cells[0] == NULL && cont->opt->start_table &&
> cont->opt->stop_table)
> {
> ! if (!opt_tuples_only && cont->opt->default_footer)
> fprintf(fout, _("(No rows)\n"));
> return;
> }
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7858: Inaccurate Log Message
On Fri, Feb 8, 2013 at 06:46:29AM +, jackie.qq.zh...@gmail.com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7858
> Logged by: Jackie Zhang
> Email address: jackie.qq.zh...@gmail.com
> PostgreSQL version: 9.1.4
> Operating system: Ubuntu
> Description:
>
> Hi,
>
> The log message of the sanity check for "superuser_reserved_connections" in
> PostmasterMain() is not accurate. See the following code snippet:
>
> /*src/backend/postmaster/postmaster.c*/
> 730 /*
> 731 * Check for invalid combinations of GUC settings.
> 732 */
> 733 if (ReservedBackends >= MaxBackends)
> 734 {
> 735 write_stderr("%s: superuser_reserved_connections must
> be less than max_connections\n", progname);
> 736 ExitPostmaster(1);
> 737 }
>
> The problem is that "MaxBackends" is calculated by both
> "autovacuum_max_workers" and "max_connections". See assign_maxconnections()
> and assign_autovacuum_max_workers() in src/backend/utils/misc/guc.c.
>
> The log message should inform both the two configuration parameters instead
> of only one -- the root cause could be the oversized
> "autovacuum_max_workers".
The calculation, ReservedBackends >= MaxBackends, was corrected in
Postgres 9.3 with this commit:
commit b3055ab4fb5839a872bfe354b2b5ac31e6903ed6
Author: Magnus Hagander
Date: Fri Aug 10 14:49:03 2012 +0200
Fix upper limit of superuser_reserved_connections, add limit for
wal_senders
Should be limited to the maximum number of connections excluding
autovacuum workers, not including.
Add similar check for max_wal_senders, which should never be higher
than
max_connections.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7844: xpath missing entity decoding - bug or feature
On Fri, Feb 1, 2013 at 12:02:41PM +, i...@fduerr.de wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7844
> Logged by: fduerr
> Email address: i...@fduerr.de
> PostgreSQL version: 9.2.2
> Operating system: Debian
> Description:
>
> Up until 9.1
>
> select (xpath('/z/text()', ('' || 'AT&T' || '')::xml))[1];
>
> returned 'AT&T'
> 9.2 returns 'AT&T'
>
> Is it a bug or a feature?
> Is there a function to decode xml-entities?
Does anyone have a comment on this?
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7836: COPY command does not honor 'FORMAT' option
On Tue, Jan 29, 2013 at 05:52:16PM -0500, Tom Lane wrote: > Jeff Janes writes: > > I figured out the need for the parenthesis by reading the docs. The > > need to also quote 'binary' really took me by surprise, especially as > > the other two formats don't need to be quoted. I assume the reason > > for this is the level of reservedness of the various words in bison. > > Yeah, BINARY is a type_func_name_keyword and hence not covered by the > copy_generic_opt_arg production. > > > But is this a doc bug? Should binary be shown > > quoted if it needs to be used quote? Or at least maybe the Example > > section could include an example of the use of FORMAT. > > Probably better to see if we can fix the grammar instead of documenting > a klugy workaround. I'm not sure we can easily allow fully-reserved > words there, but type_func_name_keyword might work. FYI, the need for single-quotes around 'binary' was removed with a patch and backpatched. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7824: documentation bug: Extract DOW
On Tue, Jan 22, 2013 at 08:40:30PM +, tim.rom...@yahoo.com wrote: > The following bug has been logged on the website: > > Bug reference: 7824 > Logged by: Tim Romano > Email address: tim.rom...@yahoo.com > PostgreSQL version: 9.2.2 > Operating system: Windows 7 x64 > Description: > > Documentation seems to indicate that only a Timestamp value is a valid > argument to the Extract function when trying to get the DOW, but a Date also > works: > > dow > The day of the week (0 - 6; Sunday is 0) (for timestamp values only) > > SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 5 [Sorry for the late reply.] I assume you mean: SELECT EXTRACT(DOW FROM DATE '2001-02-16'); date_part --- 5 (1 row) That does work fine, and is documented in PG 9.2: http://www.postgresql.org/docs/9.2/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp, --> time, or interval. (Expressions of type date are cast to timestamp and --> can therefore be used as well.) Where did you see that DATE is not supported for EXTRACT? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7817: psql does not relate to footer settings in extended mode
On Sun, Jan 20, 2013 at 10:33:37AM +, emes...@redhat.com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7817
> Logged by: Eli Mesika
> Email address: emes...@redhat.com
> PostgreSQL version: 9.1.7
> Operating system: Fedora 16
> Description:
>
> psql does not relate to footer settings in extended mode
> Sometimes we need to run a sql command withot generating header and footer.
> This can be done using the -t flag and --pset=footer=off
> The problem is that the footer is still diaplyed even if it was set to off
> if we use the extended mode for the query (-x flag)
>
> Steps to Reproduce:
> 1) create a table without any data
> for example
> create table text(i int);
> 2) run
> psql -U -t --pset=footer=off
> 3) No output is generated
> 4) run
> psql -U -t --pset=footer=off -x
> 5) Output generated : "(No Rows)"
>
> Actual results:
> psql does not honour the footer settings when output is defined to be in
> Extended Mode
>
> Expected results:
> psql should not generate any output is query has no results and -t and
> --pset=footer=off were given
This has been fixed in PG 9.3 (released today) for the specific options
you supplied:
$ psql -t --pset=footer=off test
Default footer is off.
psql (9.4devel)
Type "help" for help.
CREATE TABLE test (x INT);
CREATE TABLE
SELECT * FROM test;
\x
Expanded display is on.
SELECT * FROM test;
Unfortunately, this did not fix the more simple case where
--pset=footer=off is specified, but not -t:
$ psql --pset=footer=off test
Default footer is off.
psql (9.4devel)
Type "help" for help.
CREATE TABLE test (x INT);
CREATE TABLE
SELECT * FROM test;
x
---
\x
Expanded display is on.
SELECT * FROM test;
--> (No rows)
The attached patch fixes this, and makes it match the rest of the output
formats, which do honor --pset=footer=off alone for footers.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/bin/psql/print.c b/src/bin/psql/print.c
new file mode 100644
index 5589cea..736225c
*** a/src/bin/psql/print.c
--- b/src/bin/psql/print.c
*** print_aligned_vertical(const printTableC
*** 1171,1177
if (cont->cells[0] == NULL && cont->opt->start_table &&
cont->opt->stop_table)
{
! if (!opt_tuples_only)
fprintf(fout, _("(No rows)\n"));
return;
}
--- 1171,1177
if (cont->cells[0] == NULL && cont->opt->start_table &&
cont->opt->stop_table)
{
! if (!opt_tuples_only && cont->opt->default_footer)
fprintf(fout, _("(No rows)\n"));
return;
}
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7730: intarray representation of empty arrays
Applied.
---
On Wed, Sep 4, 2013 at 03:01:50PM -0400, Bruce Momjian wrote:
> On Wed, Dec 5, 2012 at 12:44:39AM +, el...@varlena.com wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 7730
> > Logged by: elein
> > Email address: el...@varlena.com
> > PostgreSQL version: 9.2.1
> > Operating system: Linux
> > Description:
> >
> > select NULLIF('{1,2,3}'::integer[] - '{3,2,1}'::integer[], '{}'::integer[]);
> > This returns an empty array. It should return NULL.
> >
> > Per RhodiumToad: the core code represents '{}' as an array with 0
> > dimensions, whereas intarray represents it as an array with 1 dimension but
> > 0 elements
> >
> > intarray should use the same standards as the core code if possible. I
> > peered at the code and don't see anything untoward but did not have time to
> > spend on it.
>
> I just got time to look at this, and it is certainly easier to see when
> you use array_dims():
>
> SELECT '{1,2,3}'::integer[] - '{3,2,1}'::integer[];
>?column?
> --
>{}
>
> SELECT array_dims('{1,2,3}'::integer[] - '{3,2,1}'::integer[]);
>array_dims
>
>[1:0]
>
> SELECT array_dims('{}'::integer[]);
>array_dims
>
>(null)
>
> This is part of the larger TODO item of how to handle empty
> >=1-dimensional empty arrays vs. zero-dimensional empty arrays, which is
> discussed here:
>
> https://wiki.postgresql.org/wiki/Todo#Arrays
> Improve handling of empty arrays
>
> In that thread, no one could find a way to create a 1-dimensional empty
> array at the SQL level, but thanks to intarray, you found a way. It is
> natural that intarray, being mostly used for one-dimensional arrays,
> would return a 1-dimensional empty array. However, besides being
> inconsistent, as you mentioned, there is also no way to dump/restore
> one-dimensional empty arrays, which is a larger concern.
>
> I have developed the attached patch to force empty intarray results to
> be zero-dimensional empty arrays, rather than 1-dimensional empty
> arrays. With this patch, a zero-dimensional empty array is returned:
>
> SELECT array_dims('{1,2,3}'::integer[] - '{3,2,1}'::integer[]);
>array_dims
>
>(null)
>
> --
> Bruce Momjian http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
> diff --git a/contrib/intarray/_int_tool.c b/contrib/intarray/_int_tool.c
> new file mode 100644
> index 8635576..fc462b2
> *** a/contrib/intarray/_int_tool.c
> --- b/contrib/intarray/_int_tool.c
> *** resize_intArrayType(ArrayType *a, int nu
> *** 246,251
> --- 246,258
> int nbytes = ARR_DATA_OFFSET(a) + sizeof(int) * num;
> int i;
>
> + /* if no elements, return a zero-dimensional array */
> + if (num == 0)
> + {
> + ARR_NDIM(a) = 0;
> + return a;
> + }
> +
> if (num == ARRNELEMS(a))
> return a;
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7799: Several configuration options in guc.c miss descriptions
Thanks. This will appear in PG 9.4.
---
On Tue, Jan 8, 2013 at 07:24:57AM +, t...@cs.ucsd.edu wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7799
> Logged by: Tianyin Xu
> Email address: t...@cs.ucsd.edu
> PostgreSQL version: 9.2.2
> Operating system: any
> Description:
>
> Hi, I just noticed that several developer options do not have descriptions
> (in src/backend/utils/misc/guc.c) with the text "No description available."
>
> I wrote the desc. according to the online doc, see below.
>
> HTH
>
>
> --- src/backend/utils/misc/guc.c
> +++ src/backend/utils/misc/guc.c.
> @@ -988,7 +988,7 @@ static struct config_bool ConfigureNamesBool[] =
> #ifdef BTREE_BUILD_STATS
> {
> {"log_btree_build_stats", PGC_SUSET, DEVELOPER_OPTIONS,
> - gettext_noop("No description available."),
> + gettext_noop("Logs system resource usage statistics
> (memory and CPU) on
> various B-tree operations."),
> NULL,
> GUC_NOT_IN_SAMPLE
> },
> @@ -1062,7 +1062,7 @@ static struct config_bool ConfigureNamesBool[] =
> #ifdef LOCK_DEBUG
> {
> {"trace_locks", PGC_SUSET, DEVELOPER_OPTIONS,
> - gettext_noop("No description available."),
> + gettext_noop("Emits information about lock usage."),
> NULL,
> GUC_NOT_IN_SAMPLE
> },
> @@ -1072,7 +1072,7 @@ static struct config_bool ConfigureNamesBool[] =
> },
> {
> {"trace_userlocks", PGC_SUSET, DEVELOPER_OPTIONS,
> - gettext_noop("No description available."),
> + gettext_noop("Emits information about user lock
> usage."),
> NULL,
> GUC_NOT_IN_SAMPLE
> },
> @@ -1082,7 +1082,7 @@ static struct config_bool ConfigureNamesBool[] =
> },
> {
> {"trace_lwlocks", PGC_SUSET, DEVELOPER_OPTIONS,
> - gettext_noop("No description available."),
> + gettext_noop("Emits information about lightweight lock
> usage."),
> NULL,
> GUC_NOT_IN_SAMPLE
> },
> @@ -1092,7 +1092,7 @@ static struct config_bool ConfigureNamesBool[] =
> },
> {
> {"debug_deadlocks", PGC_SUSET, DEVELOPER_OPTIONS,
> - gettext_noop("No description available."),
> + gettext_noop("Dumps information about all current locks
> when a deadlock
> timeout occurs."),
> NULL,
> GUC_NOT_IN_SAMPLE
> },
> @@ -1828,8 +1828,8 @@ static struct config_int ConfigureNamesInt[] =
> #ifdef LOCK_DEBUG
> {
> {"trace_lock_oidmin", PGC_SUSET, DEVELOPER_OPTIONS,
> - gettext_noop("No description available."),
> - NULL,
> + gettext_noop("Sets the minimum OID of tables for
> tracking locks."),
> + gettext_noop("Is used to avoid output on system
> tables."),
> GUC_NOT_IN_SAMPLE
> },
> &Trace_lock_oidmin,
> @@ -1838,7 +1838,7 @@ static struct config_int ConfigureNamesInt[] =
> },
> {
> {"trace_lock_table", PGC_SUSET, DEVELOPER_OPTIONS,
> - gettext_noop("No description available."),
> + gettext_noop("Sets the OID of the table with
> unconditionally lock
> tracing."),
> NULL,
> GUC_NOT_IN_SAMPLE
> },
>
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7754: Contrib start scipt comment refers to dead URL
On Sat, Dec 15, 2012 at 03:31:55AM +, pg-...@snkmail.com wrote: > The following bug has been logged on the website: > > Bug reference: 7754 > Logged by: Gavan Schneider > Email address: pg-...@snkmail.com > PostgreSQL version: 9.2.2 > Operating system: OSX > Description: > > FILE: > postgresql-9.2.2/contrib/start-scripts/osx/PostgreSQL ; and > many previous versions > > PBOBLEM: > The comment: > # For more information on Darwin/Mac OS X startup bundles, see this > article: > # > # > http://www.opensource.apple.com/projects/documentation/howto/html/SystemStarter_HOWTO.html > # > refers to a dead link. > > I wrote to Apple who confirmed it was dead, implicitly declined my request > for a redirection, and suggested I report here. > > Suggest this bit of text be snipped as the underlying methodology has been > deprecated for years, and is likely to be dropped soon. Specifically there > seems little point educating those who don't already know this method. Done. The fix will appear in 9.3.1. > OBSERVATION: > I am thinking this contribution should be enhanced/replaced with a method > suitable for current versions of OSX? So far I have not found a working > recipe for my situation (and I have tried quite a few) so feel > poorly placed to offer anything just now. Happy to trial ideas as suggested. > Esp. since I seem to have invented a 'tough' environment :( > > Could you point me to an active postgresql discussion of this, so I can get > up to speed (still pretty new to the pg community). Sorry, I have not seen this discussed anywhere. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7730: intarray representation of empty arrays
On Wed, Dec 5, 2012 at 12:44:39AM +, el...@varlena.com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7730
> Logged by: elein
> Email address: el...@varlena.com
> PostgreSQL version: 9.2.1
> Operating system: Linux
> Description:
>
> select NULLIF('{1,2,3}'::integer[] - '{3,2,1}'::integer[], '{}'::integer[]);
> This returns an empty array. It should return NULL.
>
> Per RhodiumToad: the core code represents '{}' as an array with 0
> dimensions, whereas intarray represents it as an array with 1 dimension but
> 0 elements
>
> intarray should use the same standards as the core code if possible. I
> peered at the code and don't see anything untoward but did not have time to
> spend on it.
I just got time to look at this, and it is certainly easier to see when
you use array_dims():
SELECT '{1,2,3}'::integer[] - '{3,2,1}'::integer[];
?column?
--
{}
SELECT array_dims('{1,2,3}'::integer[] - '{3,2,1}'::integer[]);
array_dims
[1:0]
SELECT array_dims('{}'::integer[]);
array_dims
(null)
This is part of the larger TODO item of how to handle empty
>=1-dimensional empty arrays vs. zero-dimensional empty arrays, which is
discussed here:
https://wiki.postgresql.org/wiki/Todo#Arrays
Improve handling of empty arrays
In that thread, no one could find a way to create a 1-dimensional empty
array at the SQL level, but thanks to intarray, you found a way. It is
natural that intarray, being mostly used for one-dimensional arrays,
would return a 1-dimensional empty array. However, besides being
inconsistent, as you mentioned, there is also no way to dump/restore
one-dimensional empty arrays, which is a larger concern.
I have developed the attached patch to force empty intarray results to
be zero-dimensional empty arrays, rather than 1-dimensional empty
arrays. With this patch, a zero-dimensional empty array is returned:
SELECT array_dims('{1,2,3}'::integer[] - '{3,2,1}'::integer[]);
array_dims
(null)
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/intarray/_int_tool.c b/contrib/intarray/_int_tool.c
new file mode 100644
index 8635576..fc462b2
*** a/contrib/intarray/_int_tool.c
--- b/contrib/intarray/_int_tool.c
*** resize_intArrayType(ArrayType *a, int nu
*** 246,251
--- 246,258
int nbytes = ARR_DATA_OFFSET(a) + sizeof(int) * num;
int i;
+ /* if no elements, return a zero-dimensional array */
+ if (num == 0)
+ {
+ ARR_NDIM(a) = 0;
+ return a;
+ }
+
if (num == ARRNELEMS(a))
return a;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7652: Documentation index lacks functions from "Additional Supplied Modules"
On Sun, Nov 11, 2012 at 06:35:35PM +, andreas.hei...@web.de wrote: > The following bug has been logged on the website: > > Bug reference: 7652 > Logged by: Andreas H. > Email address: andreas.hei...@web.de > PostgreSQL version: 9.2.1 > Operating system: any > Description: > > It seems, that none of the functions in the section "Additional Supplied > Modules" are listed in the index. I checked both the "intagg" module and the > "dblink" module. > > To bad if you are looking the documentation of a function but don't know > which module provides it (if any at all). FYI, this will be corrected in 9.3. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8335: trim() un-document behaviour
On Fri, Aug 9, 2013 at 11:06:15AM -0400, Tom Lane wrote:
> Bruce Momjian writes:
> > The attached patch swaps the arguments in the parser, and allows your
> > expected behavior:
>
> This patch is completely unsafe. It will break stored rules, which may
> contain calls using the existing argument order (which will be dumped
> without any of the SQL-spec syntactic sugar). To say nothing of existing
> applications that may be relying on calling the underlying functions with
> their existing argument order.
>
> The inconsistency in argument order is unfortunate but we're long since
> stuck with it, I'm afraid.
Yes, I have thought about this some more and another problem is that
rtrim/btrim/ltrim() use the source string first, so having trim() have
the source string second when using a comma is very confusing, e.g.:
-- with patch
SELECT trim('x', 'xabcx');
btrim
---
abc
-- btrim
SELECT btrim('xabcx', 'x');
btrim
---
abc
I think we can either document what we have, or remove the ability to
use comma with trim(). If we go with documentation, it is going to look
confusing as the optional modifier is going to be on the source string,
e.g.:
SELECT trim(both 'xabcx', 'x');
btrim
---
abc
We could modify the grammar to force the modifier on the second
argument, but that is more parser states for limited value.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8335: trim() un-document behaviour
On Fri, Jul 26, 2013 at 02:23:10AM +, am...@amutu.com wrote:
> in the postgresql doc 9.4,I find the trim() function like this:
>
>
> trim([leading | trailing | both] [characters] from string)
>
>
> so the trim should be pass only one argument with some optional prefix ---
> but I
> find the following calls with two argument is successfull but the results is
> unexpected and wired:
>
>
> ##first call
> postgres=# select trim(trailing '/, 'fasd/');
> rtrim
> --
>
>
> (1 row)
> -!!!note: it return titile is rtrim
>
>
> ## second call
> postgres=# select trim('/', 'fasd/')
> ;
> btrim
> -
>
>
> (1 row)
> -!!!note: it return titile is btrim
>
>
> it seems trim is transform to rtrim internal but the above call should
> return error or it may produce un-expect results
(I have cleaned up this posting because single-quotes were converted to
Unicode forward-backward quotes):
What is happening is that TRIM() is converted by the parser to calls to
base functions, e.g.
\df *trim*
List of functions
Schema | Name | Result data type | Argument data types | Type
+---+--+-+
pg_catalog | btrim | bytea| bytea, bytea| normal
pg_catalog | btrim | text | text| normal
pg_catalog | btrim | text | text, text | normal
pg_catalog | ltrim | text | text| normal
pg_catalog | ltrim | text | text, text | normal
pg_catalog | rtrim | text | text| normal
pg_catalog | rtrim | text | text, text | normal
That is why the headings don't say 'trim', but 'btrim', or similar ---
not sure we can easily improve that, and you can change the label with
AS.
The larger problem is the use of ',' instead of FROM, and the backwards
interpretation of the arguments. The query:
SELECT trim('/' FROM 'fasd/')
is internally converted to:
SELECT btrim('fasd/', '/')
Note the arguments are reversed. The comma syntax does not reverse the
arguments:
SELECT trim('/', 'fasd/')
is internally converted to:
SELECT btrim('/', 'fasd/')
You can even use modifiers like TRAILING with comma syntax:
SELECT trim(TRAILING '/', 'fasd/');
and that uses 'rtrim', but of course the behavior is still reverse of
expected.
Basically the odd comma behavior is because without a FROM, the
arguments are passed directly to btrim/rtrim/ltrim, and these functions
take the origin string first, then the string of characters to remove.
You are right this is undocumented.
The attached patch swaps the arguments in the parser, and allows your
expected behavior:
SELECT trim('x', 'xfasdx');
btrim
---
fasd
Another option would be to change the C API for the b/r/ltrim functions,
or disallow the use of the comma TRIM syntax in the parser.
I am a little worried people might be relying on the trim/comma syntax
somewhere.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
new file mode 100644
index 22e82ba..8419559
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*** substr_for: FOR a_expr{ $$ = $2;
*** 11993,11999
trim_list: a_expr FROM expr_list { $$ = lappend($3, $1); }
| FROM expr_list { $$ = $2; }
! | expr_list{ $$ = $1; }
;
in_expr: select_with_parens
--- 11993,12000
trim_list: a_expr FROM expr_list { $$ = lappend($3, $1); }
| FROM expr_list { $$ = $2; }
! | a_expr ',' a_expr { $$ = list_make2($3, $1); }
! | a_expr{ $$ = list_make1($1); }
;
in_expr: select_with_parens
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] 9.3beta2: Failure to pg_upgrade
On Fri, Aug 2, 2013 at 11:20:37PM -0400, Jesse Denardo wrote: > Alvaro, > > I applied the patch and tried upgrading again, and everything seemed to work > as > expected. We are now up and running the beta! Yeah, great, thanks everyone! -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8293: There are no methods to convert json scalar text to text in v9.3 beta2
On Wed, Jul 10, 2013 at 07:07:54PM +, jaroslav.pota...@gmail.com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 8293
> Logged by: Yaroslav Potapov
> Email address: jaroslav.pota...@gmail.com
> PostgreSQL version: Unsupported/Unknown
> Operating system: All
> Description:
>
> SELECT '"a\"b"'::json::text
>
>
> returns text: '"a\"b"' ,
> but it must return 'a"b' in my opinion.
I see you didn't get a reply, so let me try. I am no JSON expert, but I
think what is happening is that the system stores "a\"b" because that is
what a JSON/Javascript interpreter would need to understand that value.
It would convert "a\"b" to a"b. If we just stored a"b, the interpreter
would throw an error on input.
You can see this a little bit using 9.3 beta to pull values based on
keys:
SELECT json_extract_path('{"\"a": "b\"c"}'::json, '"a');
json_extract_path
---
"b\"c"
(1 row)
SELECT json_extract_path('{"\"a": "b\"c"}'::json, '\"a');
json_extract_path
---
(1 row)
Notice the key is "a, not \"a.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] 9.3beta2: Failure to pg_upgrade
On Wed, Jul 31, 2013 at 12:56:33PM -0400, Alvaro Herrera wrote: > Bruce Momjian escribió: > > On Tue, Jul 30, 2013 at 10:17:52AM -0400, Jesse Denardo wrote: > > > So, first, this is new in 9.3, and second, it seems the comment "we need > > to reset pg_control so that the new server doesn't attempt to read > > multis older than the cutoff value" is not working. Alvaro, can you > > comment on this? I think you added this code with this commit: > > So it seems. I will have a look. Well, the good news is that this is new 9.3 code, this bug was caught during beta, and pg_upgrade failed visibly, rather than silently. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] 9.3beta2: Failure to pg_upgrade
On Tue, Jul 30, 2013 at 10:17:52AM -0400, Jesse Denardo wrote:
> Name: Jesse Denardo
> Release: 9.2.2 -> 9.3beta2
> Test Type: Install/Upgrade Test
> Test Detail: pg_upgrade in a fresh install of 9.3beta2
> Platform: Debian Linux 6.0.5
> Installation Method: From source
> Platform Detail: Debian Linux 6.0.5, 2.6.32.45-grsec-2.2.2-r3, x86_64
> Test Procedure:
>
> I made a byte for byte copy of our exsting 9.2.2 Postgres directory (which
> includes the data directory), changed the port, and started it up. I pointed
I assume you did this while the server was down.
> command: "/home/postgres/9.3beta2/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
> "/home/postgres/9.3beta2/data" -o "-p 50432 -b -c synchronous_commit=off -c
> fsync=off -c full_page_writes=off -c listen_addresses='' -c
> unix_socket_permissions=0700 -c unix_socket_directories='/home/postgres'"
> start
> >> "pg_upgrade_server.log" 2>&1
> waiting for server to startLOG: database system was shut down at
> 2013-07-30 09:57:58 EDT
> FATAL: could not access status of transaction 2983
> DETAIL: Could not read from file "pg_multixact/offsets/" at offset 8192:
> Success.
OK, I actually have an idea on this. Here is the pg_upgrade code:
/*
* If the old server is before the MULTIXACT_FORMATCHANGE_CAT_VER change
* (see pg_upgrade.h) and the new server is after, then we don't copy
* pg_multixact files, but we need to reset pg_control so that the new
* server doesn't attempt to read multis older than the cutoff value.
*/
if (old_cluster.controldata.cat_ver >= MULTIXACT_FORMATCHANGE_CAT_VER &&
new_cluster.controldata.cat_ver >= MULTIXACT_FORMATCHANGE_CAT_VER)
{
copy_subdir_files("pg_multixact/offsets");
copy_subdir_files("pg_multixact/members");
prep_status("Setting next multixact ID and offset for new cluster");
/*
* we preserve all files and contents, so we must preserve both "next"
* counters here and the oldest multi present on system.
*/
exec_prog(UTILITY_LOG_FILE, NULL, true,
"\"%s/pg_resetxlog\" -O %u -m %u,%u \"%s\"",
new_cluster.bindir,
old_cluster.controldata.chkpnt_nxtmxoff,
old_cluster.controldata.chkpnt_nxtmulti,
old_cluster.controldata.chkpnt_oldstMulti,
new_cluster.pgdata);
check_ok();
}
and the C comment is:
/*
* pg_multixact format changed in 9.3 commit
0ac5ad5134f2769ccbaefec73844f85,
* ("Improve concurrency of foreign key locking") which also updated
catalog
* version to this value. pg_upgrade behavior depends on whether old
and new
* server versions are both newer than this, or only the new one is.
*/
#define MULTIXACT_FORMATCHANGE_CAT_VER 201301231
So, first, this is new in 9.3, and second, it seems the comment "we need
to reset pg_control so that the new server doesn't attempt to read
multis older than the cutoff value" is not working. Alvaro, can you
comment on this? I think you added this code with this commit:
commit 0ac5ad5134f2769ccbaefec73844f8504c4d6182
Author: Alvaro Herrera
Date: Wed Jan 23 12:04:59 2013 -0300
...
pg_upgrade also needs to be careful to copy pg_multixact files over from
the old server to the new, or at least part of multixact.c state,
depending on the versions of the old and new servers.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7709: psql \copy csv terminates with \. in quoted region
On Tue, Nov 27, 2012 at 12:33:44PM -0500, Tom Lane wrote:
> tgarn...@panjiva.com writes:
> > psql \copy terminates at \. by itself in a line even if the format is csv
> > and the \. is inside a quoted region. This means that some values can't be
> > round-tripped by psql \copy. Tested and the native postgresql COPY handles
> > this case correctly.
>
> Ugh. This seems like a rather fundamental oversight in the CSV feature.
> The problem is that psql has no idea whether the copy is being done in
> CSV mode or not --- and even if it did, it doesn't parse the data fully
> enough to realize whether a \. line is inside quotes or not.
>
> In the case of out-of-line data files, it might be reasonable to just
> dispense with the check for \. altogether and always ship the whole file
> to the backend; I think there's a \. check on the backend side. (Not
> sure this is safe in V2 protocol, but I doubt anyone cares anymore
> about that.)
>
> In the case of in-line data in a script file, CSV mode seems a bit
> broken in any case; there's no concept of a terminator in CSV, AFAIK.
> So maybe we don't have to worry about that.
I have added a C comment documenting this bug; patch attached.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/bin/psql/copy.c b/src/bin/psql/copy.c
new file mode 100644
index b5732c7..c1e7cfe
*** a/src/bin/psql/copy.c
--- b/src/bin/psql/copy.c
*** handleCopyIn(PGconn *conn, FILE *copystr
*** 635,640
--- 635,645
/* check for EOF marker, but not on a partial line */
if (firstload)
{
+ /*
+ * This code erroneously assumes '\.' on a line alone
+ * inside a quoted CSV string terminates the \copy.
+ * http://www.postgresql.org/message-id/e1tdnvq-0001ju...@wrigleys.postgresql.org
+ */
if (strcmp(buf, "\\.\n") == 0 ||
strcmp(buf, "\\.\r\n") == 0)
{
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7650: DROP TABLE IF EXISTS fails if schema does not exist
On Sun, Nov 11, 2012 at 03:17:34PM +, j...@zer7.com wrote: > The following bug has been logged on the website: > > Bug reference: 7650 > Logged by: James Bellinger > Email address: j...@zer7.com > PostgreSQL version: 9.1.5 > Operating system: Ubuntu Linux 12.04 Server > Description: > > Not sure this worked last time (I got some DMARC error), but here's a > bug... > > If a schema does not exist, DROP TABLE IF EXISTS schema.table will cause the > transaction to fail (due to 'schema does not exist'). End result, DROP TABLE > IF EXISTS is pretty much unusable with schemas. It needs to act the same as > DROP TABLE IF EXISTS does for the default schema. FYI, this will be fixed in Postgres 9.3. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7636: \ef doesn't honor ON_ERROR_ROLLBACK
On Thu, Nov 1, 2012 at 02:04:56PM +, pgm...@joh.to wrote: > The following bug has been logged on the website: > > Bug reference: 7636 > Logged by: Marko Tiikkaja > Email address: pgm...@joh.to > PostgreSQL version: 9.1.6 > Operating system: OSX something > Description: > > Hi, > > It looks like \ef doesn't honor ON_ERROR_ROLLBACK: > > =# \set ON_ERROR_ROLLBACK true > =# BEGIN; > BEGIN > =# \ef error > ERROR: function "error" does not exist > =# select 1; > ERROR: current transaction is aborted, commands ignored until end of > transaction block I think ON_ERROR_ROLLBACK only controls errors in user queries, not errors in psql operations. Sorry. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6528: pglesslog still referenced in docs, but no 9.1 support
On Thu, Jan 24, 2013 at 06:45:02PM -0500, Bruce Momjian wrote: > On Thu, Jan 24, 2013 at 08:43:37PM -0300, Alvaro Herrera wrote: > > Bruce Momjian escribió: > > > > > > On Tue, Sep 4, 2012 at 02:01:54PM -0400, Bruce Momjian wrote: > > > > On Tue, Sep 4, 2012 at 12:49:40PM -0500, Kevin Grittner wrote: > > > > > Bruce Momjian wrote: > > > > > > On Tue, Sep 4, 2012 at 12:11:53PM -0500, Kevin Grittner wrote: > > > > > > > > > > >> What do you think would be the right thing to do with it at this > > > > > >> point? > > > > > > > > > > > > Well, there should probably be a tar files with a README. What > > > > > > about adding this to /contrib? > > > > > > > > > > I would be happy to add it to contrib if people want it there. > > > > > There was some discussion of that when I wrote it, but then there > > > > > was a feeling that it would be uninteresting once people had > > > > > streaming replication. I'm skeptical that people will ever lose > > > > > interest in archiving WAL files, because of the not-uncommon need to > > > > > keep archival backups or restore to a "point in time" to recover > > > > > from a mangled table. As long as those needs are there, I suspect > > > > > that people will want to compress WAL files, including making > > > > > partially-filled files smaller. > > > > > > > > Agreed. Please move forward on the contrib idea. > > > > > > Kevin, did this get accomplished? > > > > Just to be clear, what we're proposing adding to contrib is > > clearxlogtail, not pglesslog, right? > > I was not clear on exactly what Kevin was going to add. Kevin? Any news on this? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Inconsistency between TO_CHAR() and TO_NUMBER()
On Fri, May 10, 2013 at 11:49:18AM -0400, Tom Lane wrote: > Patryk Kordylewski writes: > > SET lc_numeric TO 'de_DE.UTF-8'; > > SET > > > SELECT > >TO_CHAR(123456789.123, 'FM99G999G999G999G999G999G999D000'), > >TO_NUMBER(TO_CHAR(123456789.123, 'FM99G999G999G999G999G999G999D000'), > > 'FM99G999G999G999G999G999G999D000'); > > to_char | to_number > > -+--- > > 123.456.789,123 | 123.456 > > (1 row) > > I looked into this, and find that the reason it misbehaves is that > NUM_numpart_from_char() will treat a '.' as being a decimal point > *without any regard to locale considerations*. So even if we have > a locale-dependent format string and a locale that says '.' is a > thousands separator, it does the wrong thing. > > It's a bit surprising nobody's complained of this before. > > I propose the attached patch. I'm slightly worried though about whether > this might break any existing applications that are (incorrectly) > depending on a D format specifier being able to match '.' regardless of > locale. Perhaps we should only apply this to HEAD and not back-patch? I never trust to_char() changes for backpatching. ;-) -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8107: How to downgrade database from 9.2.3 to 8.4 ?
On Tue, Apr 23, 2013 at 04:18:17PM +, roberto.menon...@netspa.it wrote: > The following bug has been logged on the website: > > Bug reference: 8107 > Logged by: Roberto > Email address: roberto.menon...@netspa.it > PostgreSQL version: 9.2.3 > Operating system: CentOS 5.6 (Final) > Description: > > Hy. > > This is not a bug but a simple request ! > > Is there a way to downgrade a database from version 9.2.3 to 8.4.x ? > > I tried a pg_dump from 9.2.3 and a pg_restore from 9.2.3 TO a 8.4.7 server. > > but I receive several errors. > > Does it exist another way ? I recommend doing a schema dump, adjusting the output file until it generates no errors, then restore that, and then restore the data. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8036: how to disable toasting
On Thu, Apr 4, 2013 at 11:12:54AM +, zahid.qua...@cloverinfotech.com wrote: > The following bug has been logged on the website: > > Bug reference: 8036 > Logged by: anonymous > Email address: zahid.qua...@cloverinfotech.com > PostgreSQL version: 9.0.5 > Operating system: windows > Description: > > how to disable toasting in postgresql 9.0 please guide See ALTER TABLE SET STORAGE. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] JRV problem with pg_upgrade
Please run pg_ctl manually on the failed cluster and get the error message reported, or find out how to make it work successfully. --- On Mon, Mar 25, 2013 at 04:19:48PM +0100, RAVALET Jacques wrote: > > > Hello, > > I'am a database administrateur and I get trouble to upgrade database postgres > from 8.4.6 to 9.2.3. > > > > During pg_upgrade, I can't start the old cluster with parameter -c > listen_addresses='' > > > > This syntax work to start the old cluster and to access to the database > > "/distrib/postgresql/app/chfdba/produit/8.4.6/bin/pg_ctl" -w -l > "pg_upgrade_server.log" -D "/distrib/postgresql/data/chorus_formulaires/data" > -o "-p 50432 -c autovacuum=off > > -c autovacuum_freeze_max_age=20 -c listen_addresses='*' -c > unix_socket_permissions=0700" start > > > > This syntax doesn't work to start the old cluster > > "/distrib/postgresql/app/chfdba/produit/8.4.6/bin/pg_ctl" -w -l > "pg_upgrade_server.log" -D "/distrib/postgresql/data/chorus_formulaires/data" > -o "-p 50432 -c autovacuum=off > > -c autovacuum_freeze_max_age=20 -c listen_addresses='' -c > unix_socket_permissions=0700" start > > > > But pg_upgrade use this "bad" syntax even I try to force -c listen_addresses= > '*' > > > > pg_upgrade -c -d /distrib/postgresql/data/chorus_formulaires/data -D /distrib/ > postgresql/data/chorus_formulaires/data/9.2.3 -b > /distrib/postgresql/app/chfdba > /produit/8.4.6/bin -B /distrib/postgresql/app/chfdba/produit/9.2.3/bin -o "-c > listen_addresses='*'" > > > > more pg_upgrade_server.log > > could not connect to old postmaster started with the command: > > "/distrib/postgresql/app/chfdba/produit/8.4.6/bin/pg_ctl" -w -l > "pg_upgrade_server.log" -D "/distrib/postgresql/data/chorus_formulaires/data" > -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=20 > > -c listen_addresses='*' -c listen_addresses='' -c > unix_socket_permissions=0700" > start > > Failure, exiting > > > > I don't know how to workaround this problem ? > > > > Best regards, > > > > Jacques ravalet > -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7888: Small issue on wiki page Round time
It is a wiki, so you can easily get an account and make the change yourself. Thanks. --- On Sun, Feb 17, 2013 at 07:14:00PM +, s...@spamik.cz wrote: > The following bug has been logged on the website: > > Bug reference: 7888 > Logged by: Jan Krajdl > Email address: s...@spamik.cz > PostgreSQL version: Unsupported/Unknown > Operating system: all > Description: > > Hi, > > I have found small issue on this wiki page: > http://wiki.postgresql.org/wiki/Round_time > > There is mistake in "alternative approach" function - select > '1970-01-01'::timestamptz results in timestamp '1970-01-01 00:00:00' with > local timezone. But function is using it as a start of epoch which was same > time but in timezone GMT. If you use this function and you have postgres > server in non GMT timezone, rounded results are in GMT timezone but they > have timezone information about local timezone. > > I'm quite noob with postgres but I tried to fix it and it looks that this > code: > > CREATE FUNCTION date_round(base_date timestamptz, round_interval INTERVAL) > RETURNS timestamptz AS $BODY$ > SELECT TIMESTAMP WITH TIME ZONE 'epoch' + (EXTRACT(epoch FROM $1)::INTEGER + > EXTRACT(epoch FROM $2)::INTEGER / 2) > / EXTRACT(epoch FROM $2)::INTEGER * EXTRACT(epoch FROM > $2)::INTEGER * INTERVAL '1 second'; > $BODY$ LANGUAGE SQL STABLE; > > is doing it better - returned time looks correct. So if you want you can > update this page. > > Regards, > > -- > Jan Krajdl > > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7884: pg_upgradecluster is terribly slow
On Fri, Feb 15, 2013 at 03:55:02PM -0800, John R Pierce wrote: > On 2/15/2013 10:27 AM, Kevin Grittner wrote: > > You might want to try the utility which *is* supported by the > PostgreSQL community, which is pg_upgrade. For speed, consider > using the -k option. I've been able to upgrade a 3 TB database in > just a few minutes with that. (Timing depends more on the number > of database objects than their size.) > > > do note this -k option only functions if the old and new cluster are on the > same mount point, so the files can be mv'd Actually, hard linked, not moved. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist
On Fri, Feb 15, 2013 at 04:06:12PM -0600, Dave Rolsky wrote: > On Fri, 15 Feb 2013, Bruce Momjian wrote: > > >On Wed, Feb 13, 2013 at 08:22:43PM +, auta...@urth.org wrote: > >>The following bug has been logged on the website: > >> > >>Bug reference: 7873 > >>Logged by: Dave Rolsky > >>Email address: auta...@urth.org > >>PostgreSQL version: 9.2.3 > >>Operating system: Linux > >>Description: > >> > >>When you pass the --clean option to pg_restore it tries to drop tables > >>without checking if they exist. This results in lots of error output. If > >>you're running pg_restore via an automated process it's very hard to > >>distinguish between these "ok" errors and real errors. > >> > >>It should be using "DROP TABLE IF EXISTS" and the equivalent for > >>constraints. > > > >Well, I think the question is whether you want error feedback for things > >that don't exist. I don't really know the answer. > > Fair enough. It should probably an option to add "if exists", at > least. I can't imagine I'm the only using this tool to ship database > updates around to different machines, some of which may not have new > tables. I'd really like to be able to know when the restore fails > versus when it succeeds but is noisy. All I can say is I don't remember anyone asking for this in the past. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7886: date_trunc(date) returning timestamptz instead of timestamp
On Fri, Feb 15, 2013 at 09:27:40PM +, nick.bax...@gmail.com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7886
> Logged by: Nick Baxter
> Email address: nick.bax...@gmail.com
> PostgreSQL version: 9.0.3
> Operating system: Linux 2.6.18
> Description:
>
> 9.9.2. indicates that date_trunc can be called with a date value (which will
> be cast to a timestamp). And regardless of the input, that the result will
> be of type timestamp. When I call it with a date, I get a timestamp with
> time zone instead, as indicated by the psql output.
>
> # select date_trunc('month',date '2013-2-15');
>date_trunc
>
> 2013-02-01 00:00:00-06
> (1 row)
That documentation often uses timestamp when it means timestamp with
time zone. Not sure why that is.
\df shows the supported functions:
test=> \df date_trunc
List of functions
Schema |Name| Result data type |
Argument data types| Type
++-+---+
pg_catalog | date_trunc | interval| text, interval
| normal
pg_catalog | date_trunc | timestamp without time zone | text,
timestamp without time zone | normal
pg_catalog | date_trunc | timestamp with time zone| text,
timestamp with time zone| normal
This returns a timestamp without time zone:
test=> select date_trunc('month',timestamp '2013-2-15');
date_trunc
-
2013-02-01 00:00:00
(1 row)
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7885: postmaster panic on startup does not release shared memory
When we panic, we PANIC, meaning we don't jump around looking for cleanup stuff, which might make things worse. Postgres should never panic, so it would be good if you found the cause of the panic. Does restarting the server remove the old shared memory stuff? --- On Fri, Feb 15, 2013 at 06:33:21PM +, david.tho...@enterprisedb.com wrote: > The following bug has been logged on the website: > > Bug reference: 7885 > Logged by: David Thomas > Email address: david.tho...@enterprisedb.com > PostgreSQL version: 9.2.3 > Operating system: CentOS 6.3 > Description: > > It seems that if the postmaster encounters a PANIC condition during startup, > it leaves it's allocated shared memory segments around: > > -bash-4.1$ ipcs -a > > -- Shared Memory Segments > keyshmid owner perms bytes nattch status > > -- Semaphore Arrays > keysemid owner perms nsems > > -- Message Queues > keymsqid owner perms used-bytes messages > > -bash-4.1$ /usr/pgsql-9.2/bin/postmaster --single -D > /var/lib/pgsql/9.2/data/ test > PANIC: could not locate a valid checkpoint record > Aborted > -bash-4.1$ ipcs -a > > -- Shared Memory Segments > keyshmid owner perms bytes nattch status > 0x0001 753664 postgres 60041279488 0 > > -- Semaphore Arrays > keysemid owner perms nsems > 0x0001 5439490postgres 60017 > 0x0002 5472259postgres 60017 > 0x0003 5505028postgres 60017 > 0x0004 5537797postgres 60017 > 0x0005 5570566postgres 60017 > 0x0006 5603335postgres 60017 > 0x0007 5636104postgres 60017 > > -- Message Queues > keymsqid owner perms used-bytes messages > > Considering that it was able to allocate this memory before the panic > occurred, it should remove them before exiting. > > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7874: GUC's not in database dumps
On Thu, Feb 14, 2013 at 01:37:04AM +, j...@tanga.com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7874
> Logged by: Joe Van Dyk
> Email address: j...@tanga.com
> PostgreSQL version: 9.2.1
> Operating system: OSX, Linux
> Description:
>
> If I run:
>alter database foo set my.name = 'joe';
>
> that GUC isn't available inside backups of that database.
>
> It does show up when doing "pg_dumpall -s".
>
> It does *not* show up when doing "pg_dumpall -g"
>
This is a TODO:
https://wiki.postgresql.org/wiki/Todo#ALTER
Refactor handling of database attributes between pg_dump and pg_dumpall
Currently only pg_dumpall emits database attributes, such as ALTER
DATABASE SET commands and database-level GRANTs. Many people wish that
pg_dump would do that. One proposal is to let pg_dump issue such
commands if the -C switch was used, but it's unclear whether that will
satisfy the demand.
http://www.postgresql.org/message-id/4864f001.50...@archonet.com
http://www.postgresql.org/message-id/11646.1272814...@sss.pgh.pa.us
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist
On Wed, Feb 13, 2013 at 08:22:43PM +, auta...@urth.org wrote: > The following bug has been logged on the website: > > Bug reference: 7873 > Logged by: Dave Rolsky > Email address: auta...@urth.org > PostgreSQL version: 9.2.3 > Operating system: Linux > Description: > > When you pass the --clean option to pg_restore it tries to drop tables > without checking if they exist. This results in lots of error output. If > you're running pg_restore via an automated process it's very hard to > distinguish between these "ok" errors and real errors. > > It should be using "DROP TABLE IF EXISTS" and the equivalent for > constraints. Well, I think the question is whether you want error feedback for things that don't exist. I don't really know the answer. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7852: TeamPostgresql is giving error
On Tue, Feb 5, 2013 at 06:30:56PM +0100, Magnus Hagander wrote:
>
> On Feb 5, 2013 6:20 PM, wrote:
> >
> > The following bug has been logged on the website:
> >
> > Bug reference: 7852
> > Logged by: Yogesh Khandelwal
> > Email address: yogesh.khandel...@emc.com
> > PostgreSQL version: 9.0.8
> > Operating system: Linux
> > Description:
> >
> > hi,
> >
> > I've deployed TeamPostgreSQL on my server and trying to access my database
> > using that and getting the following error while trying to access the
> > schema/tables
>
> Team PostgreSQL is a third party product, and you need to bring this up with
> their support, since it's not an issue in postgresql.
FYI, TeamPostgreSQL has this to the bottom of their web pages to avoid
confusion:
TeamPostgreSQL is a project of Webworks (webworks.dk), and is not
affiliated with the PostgreSQL Project.
The PostgreSQL trademark is used with permission.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6528: pglesslog still referenced in docs, but no 9.1 support
On Thu, Jan 24, 2013 at 08:43:37PM -0300, Alvaro Herrera wrote: > Bruce Momjian escribió: > > > > On Tue, Sep 4, 2012 at 02:01:54PM -0400, Bruce Momjian wrote: > > > On Tue, Sep 4, 2012 at 12:49:40PM -0500, Kevin Grittner wrote: > > > > Bruce Momjian wrote: > > > > > On Tue, Sep 4, 2012 at 12:11:53PM -0500, Kevin Grittner wrote: > > > > > > > > >> What do you think would be the right thing to do with it at this > > > > >> point? > > > > > > > > > > Well, there should probably be a tar files with a README. What > > > > > about adding this to /contrib? > > > > > > > > I would be happy to add it to contrib if people want it there. > > > > There was some discussion of that when I wrote it, but then there > > > > was a feeling that it would be uninteresting once people had > > > > streaming replication. I'm skeptical that people will ever lose > > > > interest in archiving WAL files, because of the not-uncommon need to > > > > keep archival backups or restore to a "point in time" to recover > > > > from a mangled table. As long as those needs are there, I suspect > > > > that people will want to compress WAL files, including making > > > > partially-filled files smaller. > > > > > > Agreed. Please move forward on the contrib idea. > > > > Kevin, did this get accomplished? > > Just to be clear, what we're proposing adding to contrib is > clearxlogtail, not pglesslog, right? I was not clear on exactly what Kevin was going to add. Kevin? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6528: pglesslog still referenced in docs, but no 9.1 support
On Tue, Sep 4, 2012 at 02:01:54PM -0400, Bruce Momjian wrote: > On Tue, Sep 4, 2012 at 12:49:40PM -0500, Kevin Grittner wrote: > > Bruce Momjian wrote: > > > On Tue, Sep 4, 2012 at 12:11:53PM -0500, Kevin Grittner wrote: > > > > >> What do you think would be the right thing to do with it at this > > >> point? > > > > > > Well, there should probably be a tar files with a README. What > > > about adding this to /contrib? > > > > I would be happy to add it to contrib if people want it there. > > There was some discussion of that when I wrote it, but then there > > was a feeling that it would be uninteresting once people had > > streaming replication. I'm skeptical that people will ever lose > > interest in archiving WAL files, because of the not-uncommon need to > > keep archival backups or restore to a "point in time" to recover > > from a mangled table. As long as those needs are there, I suspect > > that people will want to compress WAL files, including making > > partially-filled files smaller. > > Agreed. Please move forward on the contrib idea. Kevin, did this get accomplished? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] [BUGS] BUG #6572: The example of SPI_execute is bogus
On Thu, Jan 24, 2013 at 04:51:04PM -0500, Tom Lane wrote: > Bruce Momjian writes: > > Would someone make the doc change outlined above? Thanks. > > Sorry, I'd nearly forgotten about this issue. Will see about fixing the > docs. (It looks like some of the comments in execMain.c could use work > too.) Thanks. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] [BUGS] BUG #6572: The example of SPI_execute is bogus
On Wed, Aug 29, 2012 at 01:13:51PM +, Rajeev rastogi wrote: > > From: pgsql-bugs-ow...@postgresql.org [pgsql-bugs-ow...@postgresql.org] on > behalf of Bruce Momjian [br...@momjian.us] > Sent: Wednesday, August 29, 2012 8:46 AM > To: Tom Lane > Cc: Robert Haas; Hitoshi Harada; pgsql-bugs@postgresql.org; > pgsql-hack...@postgresql.org > Subject: Re: [HACKERS] [BUGS] BUG #6572: The example of SPI_execute is bogus > > On Sun, Apr 15, 2012 at 12:29:39PM -0400, Tom Lane wrote: > > Robert Haas writes: > > > On Thu, Apr 5, 2012 at 2:39 AM, Hitoshi Harada > > > wrote: > > >> On Wed, Apr 4, 2012 at 8:00 AM, Tom Lane wrote: > > >>> Given the lack of complaints since 9.0, maybe we should not fix this > > >>> but just redefine the new behavior as being correct? But it seems > > >>> mighty inconsistent that the tuple limit would apply if you have > > >>> RETURNING but not when you don't. In any case, the ramifications > > >>> are wider than one example in the SPI docs. > > > > >> To be honest, I was surprised when I found tcount parameter is said to > > >> be applied to even INSERT. I believe people think that parameter is > > >> to limit memory consumption when returning tuples thus it'd be applied > > >> for only SELECT or DML with RETURNING. So I'm +1 for non-fix but > > >> redefine the behavior. Who wants to limit the number of rows > > >> processed inside the backend, from SPI? > > > > > Yeah. > > > > Okay, apparently nobody cares about RETURNING behaving differently from > > non-RETURNING, so the consensus is to redefine the current behavior as > > correct. That means what we need is to go through the docs and see what > > places need to be updated (and, I guess, back-patch the changes to 9.0). > > I will get to this if nobody else does, but not right away. > > > Would someone make the doc change outlined above? Thanks. > > > I would like to work on this documentation bug. > As per analysis I am planning to update following SPI function: > 1. SPI_Execute: Here we will mention that argument count is used only for the > kind of command which returns result i.e. all kind of SELECT and DML with > returning clause. count is ignored for any other kind of commands. I will add > one example also to indicate the difference. > 2. SPI_execute_plan_with_paramlist: Here we can give just reference to > SPI_execute i.e. I will mention that count has same interpretation as in > SPI_execute. > 3. SPI_execp: Here we can give just reference to SPI_execute i.e. I will > mention that count has same interpretation as in SPI_execute. Would someone please provide answers to these questions, or write a patch? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7815: Upgrading PostgreSQL from 9.1 to 9.2 with pg_upgrade/postgreql-setup fails - invalid status retrieve
On Sat, Jan 19, 2013 at 11:27:28AM -0500, Tom Lane wrote:
> Bruce Momjian writes:
> > Why is a clean shutdown important? If the server crashed, we would have
> > committed transactions in the WAL files which are not transfered to the
> > new server, and would be lost.
>
> > I am hesistant to even start such an old server because pg_upgrade never
> > modifies the old server. Even starting it in that case would be
> > modifying it.
>
> I'm not really following this logic. If the old cluster was in a
> crashed state, why would we not expect that starting a postmaster would
> be the best (only) way to repair the damage and make everything good
> again? Isn't that exactly what the user would have to do anyway? What
> other action would you expect him to take instead?
>
> (But, at least with the type of packaging I'm using in Fedora, he would
> first have to go through a package downgrade/reinstallation process,
> because the packaging provides no simple scripted way of manually
> starting the old postgres executable, only the new one. Moreover, what
> pg_upgrade is printing provides no help in figuring out whether that's
> the next step.)
>
> I do sympathize with taking a paranoid attitude here, but I'm failing
> to see what advantage there is in not attempting to start the old
> postmaster. In the *only* case that pg_upgrade is successfully
> protecting against with this logic, namely there's-an-active-postmaster-
> already, the postmaster is equally able to protect itself. In other
> cases it would be more helpful not less to let the postmaster analyze
> the situation.
>
> > The other problem is that if the server start fails, how do we know if
> > the failure was due to a running postmaster?
>
> Because we read the postmaster's log file, or at least tell the user to
> do so. That report would be unambiguous, unlike pg_upgrade's.
Attached is a WIP patch to give you an idea of how I am going to solve
this problem. This comment says it all:
! /*
!* If we have a postmaster.pid file, try to start the server. If
!* it starts, the pid file was stale, so stop the server. If it
!* doesn't start, assume the server is running.
!*/
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index 1780788..25cf23c
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*** check_and_dump_old_cluster(bool live_che
*** 78,84
/* -- OLD -- */
if (!live_check)
! start_postmaster(&old_cluster);
set_locale_and_encoding(&old_cluster);
--- 78,84
/* -- OLD -- */
if (!live_check)
! start_postmaster(&old_cluster, true);
set_locale_and_encoding(&old_cluster);
*** issue_warnings(char *sequence_script_fil
*** 201,207
/* old = PG 8.3 warnings? */
if (GET_MAJOR_VERSION(old_cluster.major_version) <= 803)
{
! start_postmaster(&new_cluster);
/* restore proper sequence values using file created from old server */
if (sequence_script_file_name)
--- 201,207
/* old = PG 8.3 warnings? */
if (GET_MAJOR_VERSION(old_cluster.major_version) <= 803)
{
! start_postmaster(&new_cluster, true);
/* restore proper sequence values using file created from old server */
if (sequence_script_file_name)
*** issue_warnings(char *sequence_script_fil
*** 224,230
/* Create dummy large object permissions for old < PG 9.0? */
if (GET_MAJOR_VERSION(old_cluster.major_version) <= 804)
{
! start_postmaster(&new_cluster);
new_9_0_populate_pg_largeobject_metadata(&new_cluster, false);
stop_postmaster(false);
}
--- 224,230
/* Create dummy large object permissions for old < PG 9.0? */
if (GET_MAJOR_VERSION(old_cluster.major_version) <= 804)
{
! start_postmaster(&new_cluster, true);
new_9_0_populate_pg_largeobject_metadata(&new_cluster, false);
stop_postmaster(false);
}
diff --git a/contrib/pg_upgrade/exec.c b/contrib/pg_upgrade/exec.c
new file mode 100644
index e326a10..2b3c203
*** a/contrib/pg_upgrade/exec.c
--- b/contrib/pg_upgrade/exec.c
*** exec_prog(const char *log_file, const ch
*** 99,104
--- 99,106
fclose(log);
result = system(cmd);
+ if (result != -1)
+ result = WEXITSTATUS(result);
umask(old_umask);
diff --git a/contrib/pg_upgrade/pg_upgrade.c b/contrib/pg_upgrade/pg_upgrade.c
new file mode 100644
index 85997e5..e329cb5
*** a/contrib/pg_upgrade/pg_upgrade.c
--- b/contrib/pg_upgrade/pg_upgrade.c
*** main(i
Re: [BUGS] BUG #7815: Upgrading PostgreSQL from 9.1 to 9.2 with pg_upgrade/postgreql-setup fails - invalid status retrieve
On Sat, Jan 19, 2013 at 10:45:15PM +0400, George Machitidze wrote: > Hi Bruce, Tom > > >The backstory on this is at the cited Red Hat bug ... apparently the OP > >decided I was clueless and he needed to consult some real authorities. > Oh come on, I'm very sure you both are good guys and know what you are doing, > none of us is ignorant bastard :) > Decided to open case here too, because of simple reason - maybe someone had > same issue, or knows how pg_upgrade works (in details) better than me, because > I am clueless. > This is test DB and I can erase it, but I'm very sure there's something wrong > in upgrade process - this is what I want to be solved. > > Now, we can open a bottle of whiskey and go back to the problem: > 1. I didn't run postmaster before/during pg_upgrade, it was never invoked > manually in this process > 2. There is no pid file AFTER application is stopped, but looks like it's > there > while pg_upgrade is running - strace showed that and there is no need to run > FAM to verify that > > I don't know how pg_upgrade works, looks like it's trying to start postmaster, > which runs, postmaster.pid is created, then postmaster fails stop or needs > some > more time bedore pg_upgrade is checking it's pid. That's what I see. > > So, is pg_upgrade starting postmaster? If yes, then when (at which step) and > why pid file check is done. That's all what we all want to know, right? The pid check is done before pg_upgrade starts or stops any postmaster, to make sure both servers are down before it starts. Tom wants that testing improved. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7815: Upgrading PostgreSQL from 9.1 to 9.2 with pg_upgrade/postgreql-setup fails - invalid status retrieve
On Sat, Jan 19, 2013 at 12:47:03AM -0500, Tom Lane wrote: > Bruce Momjian writes: > > On Sat, Jan 19, 2013 at 12:02:31AM -0500, Tom Lane wrote: > >> In the meantime, I was wondering a bit why pg_upgrade looks at the > >> postmaster.pid file at all. > > > The reason we check for postmaster.pid is so we can give the user a clue > > about which postmaster is running. > > [ scratches head... ] I failed to detect any such clue in the error > message it prints. Had you printed the PID from the file, or even > better looked to see if that process was actually still alive, this > argument would be reasonable. But pg_upgrade does neither of those, > whereas if it had started a postmaster the postmaster would have done > both of those things. > > > Also, we don't want to start on a non-clean shutdown, so the missing pid > > file tells us it was clean. > > I agree that super paranoia is not unreasonable in pg_upgrade. But it > would be useful to print something similar to what the backend prints, > about checking whether PID N is still there and manually removing the > lock file if not. Or (ahem) you could let the existing backend-side > logic do that for you, rather than reimplementing that logic badly. The current output is: There seems to be a postmaster servicing the old cluster. Please shutdown that postmaster and try again. You are right that it is inaccurate. I should reword that to say the server is running or was not properly shut down: There seems to be a postmaster servicing the old cluster, or it was not properly shut down. Please cleanly shutdown that postmaster and try again. Why is a clean shutdown important? If the server crashed, we would have committed transactions in the WAL files which are not transfered to the new server, and would be lost. I am hesistant to even start such an old server because pg_upgrade never modifies the old server. Even starting it in that case would be modifying it. The other problem is that if the server start fails, how do we know if the failure was due to a running postmaster? I could later check the postmaster.pid file, but it might have failed not yet getting to the section where we remove that file. The server-still-running is a common cause of failure, so I wanted something that was very clear, rather than a generic can't-start-the-server. I am open to ideas. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7815: Upgrading PostgreSQL from 9.1 to 9.2 with pg_upgrade/postgreql-setup fails - invalid status retrieve
On Sat, Jan 19, 2013 at 12:02:31AM -0500, Tom Lane wrote:
> Bruce Momjian writes:
> > On Fri, Jan 18, 2013 at 10:19:48PM +, gio...@gmail.com wrote:
> >> https://bugzilla.redhat.com/show_bug.cgi?id=896161
> >> Upgrading PostgreSQL from 9.1 to 9.2 with pg_upgrade/postgreql-setup fails
> >> with invalid message "There seems to be a postmaster servicing the old
> >> cluster". Looks like pg_upgrade is checking pid file too early without
> >> waiting for master process to exit:
> >>
> >> open("/var/lib/pgsql/data-old/postmaster.pid", O_RDONLY) = 5
>
> > How are you shutting down the postmaster? Are you use pg_ctl -w stop?
> > If not, you have to wait for the server to actually shut down before
> > starting pg_upgrade. pg_upgrade is not going to do that waiting.
>
> The backstory on this is at the cited Red Hat bug ... apparently the OP
> decided I was clueless and he needed to consult some real authorities.
Yes, it was clear there was some backstory in reading that thread.
> The existing pg_control clearly says that the cluster was shut down,
> so it's not clear why there's still a postmaster.pid file there.
> There's some debugging to be done yet about how that got to be that way.
> (AFAICS the RPM upgrade process ought to shut down the old postmaster
> before installing a new one; but somehow that went wrong, or else a
> doppelganger postmaster.pid rose from the dead. Anyway, that's not a
> matter for this list because it involves Red Hat upgrade processes, not
> anything supplied by the community.)
>
> In the meantime, I was wondering a bit why pg_upgrade looks at the
> postmaster.pid file at all. Generally we recommend that startup scripts
> *not* look at the lock file but just try to start a postmaster, and
> leave it to the postmaster to decide if there's a valid lockfile
> present. Is it really appropriate for pg_upgrade to do this
> differently? I think the complained-of case would have gone through
> cleanly if that error check weren't there, or in any case the postmaster
> would have done a better job of checking for a conflicting postmaster.
The reason we check for postmaster.pid is so we can give the user a clue
about which postmaster is running. We want to make sure everything is
super-clean before we do anything. What we could do is to first try to
start each cluster, and then fail if the start fails, but the start
could fail for all sorts of reasons so it doesn't really seems like a
win.
Also, we don't want to start on a non-clean shutdown, so the missing pid
file tells us it was clean.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7815: Upgrading PostgreSQL from 9.1 to 9.2 with pg_upgrade/postgreql-setup fails - invalid status retrieve
On Fri, Jan 18, 2013 at 10:19:48PM +, gio...@gmail.com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7815
> Logged by: George Machitidze
> Email address: gio...@gmail.com
> PostgreSQL version: 9.2.2
> Operating system: Fedora 18 Linux
> Description:
>
> https://bugzilla.redhat.com/show_bug.cgi?id=896161
> Upgrading PostgreSQL from 9.1 to 9.2 with pg_upgrade/postgreql-setup fails
> with invalid message "There seems to be a postmaster servicing the old
> cluster". Looks like pg_upgrade is checking pid file too early without
> waiting for master process to exit:
>
> open("/var/lib/pgsql/data-old/postmaster.pid", O_RDONLY) = 5
How are you shutting down the postmaster? Are you use pg_ctl -w stop?
If not, you have to wait for the server to actually shut down before
starting pg_upgrade. pg_upgrade is not going to do that waiting.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7750: pid file conflict in RedHat
On Tue, Dec 11, 2012 at 12:28:40PM -0500, Tom Lane wrote:
> Bruce Momjian writes:
> > On Mon, Dec 10, 2012 at 06:41:29PM +, postg...@tbruce.com wrote:
> >> In line 238 of the rpm based init.d script, the pid file is called
> >> specifically with a qualified path ("status -p
> >> /var/run/postmaster-9.1.pid"), even though line 70 specifies a variable to
> >> point to the pid file (pidfile="/var/run/postmaster-9.1.pid").
> >>
> >> This is with rpm version of postgres 9.1.4 and 9.1.6 (we haven't tested
> >> 9.1.7 at this time).
>
> > I think you need to report this to Red Hat; we didn't create that file.
>
> Red Hat didn't either. This report is evidently about the PGDG RPMs.
Yes, I have found it is a PGDG RPM and forwarded it to Devrim.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7750: pid file conflict in RedHat
On Mon, Dec 10, 2012 at 06:41:29PM +, postg...@tbruce.com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7750
> Logged by: Timothy J Bruce
> Email address: postg...@tbruce.com
> PostgreSQL version: 9.1.6
> Operating system: RedHat v6.2
> Description:
>
> In line 238 of the rpm based init.d script, the pid file is called
> specifically with a qualified path ("status -p
> /var/run/postmaster-9.1.pid"), even though line 70 specifies a variable to
> point to the pid file (pidfile="/var/run/postmaster-9.1.pid").
>
> This is with rpm version of postgres 9.1.4 and 9.1.6 (we haven't tested
> 9.1.7 at this time).
I think you need to report this to Red Hat; we didn't create that file.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7722: extract(epoch from age(...)) appears to be broken
On Mon, Dec 3, 2012 at 03:05:57AM -0300, Alvaro Herrera wrote:
> aanisi...@inbox.ru wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 7722
> > Logged by: Artem Anisimov
> > Email address: aanisi...@inbox.ru
> > PostgreSQL version: 9.2.1
> > Operating system: Slackware Linux 14.0/amd64
> > Description:
> >
> > The following to queries give the same result (first arguments to age()
> > differ in the day number only, second arguments are identical):
> >
> > select extract(epoch from age('2012-11-23 16:41:31', '2012-10-23
> > 15:56:10'));
> >
> > and
> >
> > select extract(epoch from age('2012-11-22 16:41:31', '2012-10-23
> > 15:56:10'));
>
> alvherre=# select age('2012-11-22 16:41:31', '2012-10-23 15:56:10');
>age
> --
> 30 days 00:45:21
> (1 fila)
>
> alvherre=# select age('2012-11-23 16:41:31', '2012-10-23 15:56:10');
> age
>
> 1 mon 00:45:21
> (1 fila)
>
> The problem is that age() returns 30 days in one case, and "one month" in the
> other; extract() then considers the month as equivalent to 30 days. This is
> documented as such, see [1].
>
> [1] http://www.postgresql.org/docs/current/static/functions-datetime.html
Wow, that is a weird case. In the first test, we count the number of
days because it is less than a full month. In the second case, we call
it a full month, but then forget how long it is. Not sure how we could
improve this.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7706: Can slave database supports creating temporary tables?
On Mon, Nov 26, 2012 at 01:59:44PM +, wil...@leyou.com wrote: > The following bug has been logged on the website: > > Bug reference: 7706 > Logged by: wilson > Email address: wil...@leyou.com > PostgreSQL version: 9.2.1 > Operating system: debian > Description: > > Hi Dears, > In M/S mode,Slave database can't create temporary tables right now,just > support read only access.But sometimes,for support some complex queries,we > need create some temp tables actually. > So I suggest postgresql slave node can support create temp tables in the > next version,that'll be great!!! Well, because the slave is read-only, we have not figured out how to enable creation of temporary tables on the slave. It isn't even on our TODO list because we have no idea how we would do it. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7683: pg_upgrade missing configuration file
On Tue, Nov 20, 2012 at 11:23:13AM +, bernhard.schra...@innogames.de wrote: > To point this out, postgresql 9.0 and 9.2 are not running, if 9.0 is > running, the 9.0 tests succeed, but afaik they shouldn't running, or at > least 9.2 shouldn't running during upgrade, but without the link to the > config it will never start. The same for 9.0, if it is shut down and the > link is present, it works. I assume this is a "bug" or at least a missing > option for pg_upgrade in addition to debian. Do you confirm? Or do i > undestand something completly wrong? I hope not, thanks in advance Have you seen this in the pg_upgrade 9.2 manual? If you are upgrading a pre-PostgreSQL 9.2 cluster that uses a configuration-file-only directory, you must pass the real data directory location to pg_upgrade, and pass the configuration directory location to the server, e.g. -d /real-data-directory -o '-D /configuration-directory'. -o, --old-options=OPTIONS old cluster options to pass to the server -O, --new-options=OPTIONS new cluster options to pass to the server -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] [GENERAL] fuzzystrmatch module buggy? observations
On Tue, Oct 30, 2012 at 02:29:09PM +0100, r d wrote:
> The fuzzystrmatch module (http://www.postgresql.org/docs/9.2/static/
> fuzzystrmatch.html) is currently, as of 9.2.1, documented with the caution "At
> present, the soundex, metaphone, dmetaphone, and dmetaphone_alt functions do
> not work well with multibyte encodings (such as UTF-8)".
>
> While the venerable algorithms contained in the module seem to generally work
> for Latin strings from European languages which all have accented/diacritic
> characters such as äöüñáéíóúàèìòù, for languages with non-Latin characters
> such
> as Kyrillic, Hebrew, Arabic, Chinese, these venerable algorithms return NULL
> (empty) or plain weirdness.
>
> Some examples:
>
> dmetaphone ('Новости') = 'NN'
> soundex ('Новости') = NULL
>
> dmetaphone ('לפחות') = NULL
> soundex ('לפחות') = NULL
>
> soundex ('相关搜索') = NULL
> dmetaphone ('相关搜索') = NULL
>
> metaphone() crashes with SQL state: 42883 for all these strings (it tells me I
> should cast the 'unknown' input).
>
> The string 'äöüñáéíóúàèìòù' causes metaphone(), dmetaphone(), dmetaphone_alt,
> soundex() to fail.
>
> Only levenshtein() appears to function correctly with all above inputs, even
> when I let it compare Hebrew against Chinese strings.
>
> Summarizing my experience:
> * for english (ASCII equivalent), the module works,
> * for the rest of the Latin charsets (equivalent to ISO 8859-x) the module
> works unreliably,
> * for non-latin chars (UTF8 with 2-4 bytes per char) the module does not work
>
> Note: My DB and the OS are set up for UTF-8.
>
> This would appear to be less a problem of Postgresql and the fuzzystrmach
> module itself but because there
> appear to exist no replacement algorithms adequate for a multilingual world -
> at least that is my impression
> after looking at the IPA and http://www.lt-world.org websites and branching
> out
> from there.
This is a very good summary. I was not aware of all these behaviors.
> Given all this I have no idea of this is a bug at all or the state-of-the-art
> around this topic is inadequate.
I have no idea either.
> Questions (to the developers):
> - Is there anything in work or planned for the fuzzystrmatch module?
> - Does anybody know about adequate replacements or upgrades of the soundex,
> metaphone etc. algorithms from academia?
I have not heard of anyone working in this area. What usually happens
is some expert in the field shows up and submits a patch to improve it.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_ctl restart issue with relative paths
On Sun, Oct 14, 2012 at 09:56:46AM -0700, Josh Kupershmidt wrote: > On Sat, Oct 13, 2012 at 3:56 PM, Thom Brown wrote: > > > I have noticed that, using pg_ctl, if you start Postgres using a > > relative path, then attempt to restart it from anywhere else, it > > fails. > > Yeah, I was complaining about the same problem here: > http://archives.postgresql.org/pgsql-bugs/2011-10/msg00210.php > > and at least there's a doc note now warning against using relative > paths, though it sure would be nice if we could fix this gripe. FYI, the doc patch is mentioned here: http://archives.postgresql.org/pgsql-bugs/2012-08/msg00133.php -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7573: data loss in corner case using delete_old_cluster.sh (pg_upgrade)
On Thu, Oct 4, 2012 at 10:40:19AM +1000, Maxim Boguk wrote: > > > Test database have a bit unusual tablespace layout: > > main tablespace partition was mounted inside data directory of the old > > cluster... > > E.g.: > > data directory - /var/lib/postgresql/9.2/main > > main tablespace (another partition mount point) - > > /var/lib/postgresql/9.2/main/largedb > > Can you show us the data directory path of the old and new clusters? > > > --old-datadir=/var/lib/postgresql/9.0/main > --new-datadir=/var/lib/postgresql/9.2/main > > second partition used as tablespace were mounted as: > /var/lib/postgresql/9.0/main/largedb > > > > pg_upgrade really doesn't know what is inside that old cluster, so it > just deletes everything under the data directory. > > > Hmm... may be good idea to try opposite way: > default directories and files layout in PostgreSQL data directory well > documented and almost never changes. > May be instead of rm -rf whole data directory try rm -rf only files and > directories which sure belong to the PostgreSQL? > > Something along with: > 1)rm -rf base global pg_clog pg_multixact ... and so on > 2)produce warning if any unusual files left in data directory after that (but > not delete them). > 3)delete data directory itself only if that directory completely empty after > step 1 and 2 > > PS: I know that solution will be not completely error-prone but it will > prevent > most probably data-loss scenarios. So it's better then nothing. > > PS: I also think deleting postgresql.conf and pg_hba.conf from old data > directory is wrong move too... if admin forget copy pg_hba.conf to the new > cluster - these settings could be lost forever after delete_old_cluster.sh . This all seems like a step backwards and adds complexity that will fail. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7573: data loss in corner case using delete_old_cluster.sh (pg_upgrade)
On Fri, Sep 28, 2012 at 01:18:26AM +, maxim.bo...@gmail.com wrote: > The following bug has been logged on the website: > > Bug reference: 7573 > Logged by: Maxim Boguk > Email address: maxim.bo...@gmail.com > PostgreSQL version: 9.2.0 > Operating system: Linux > Description: > > Hi, > > today while performing migration of test database (with no critical data... > and that was good thing). > I found very nasty corner case with using delete_old_cluster.sh after > pg_upgrade. > > Test database have a bit unusual tablespace layout: > main tablespace partition was mounted inside data directory of the old > cluster... > E.g.: > data directory - /var/lib/postgresql/9.2/main > main tablespace (another partition mount point) - > /var/lib/postgresql/9.2/main/largedb Can you show us the data directory path of the old and new clusters? pg_upgrade really doesn't know what is inside that old cluster, so it just deletes everything under the data directory. I guess I could check if the path of the old cluster somehow matches the leading path of the new cluster, but I doubt that would be fool-proof either, e.g. symlinks. > May be it is good idea to add: >--one-file-system > when removing a hierarchy recursively, skip any directory that > is on a file system different from that of the corresponding command line > argument > > to rm call into that script. > > However, it is Linux only feature. > > PS: Yes I know that keeping any foreign data inside PostgreSQL data > directory is bad idea. I don't see how adding --one-file-system would help us. They could have place it under the old cluster in the same file system. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7549: max_connections check should query master when starting standby
On Tue, Sep 18, 2012 at 09:09:13PM +0900, Fujii Masao wrote: > On Tue, Sep 18, 2012 at 3:48 PM, Heikki Linnakangas > wrote: > > On 18.09.2012 09:46, Craig Ringer wrote: > >> > >> On 09/18/2012 07:57 AM, Fujii Masao wrote: > >>> > >>> If you change the max_connections on the master, you need to take a > >>> fresh backup from the master and start the standby from it. > >> > >> > >> WTF, really? > > > > > > No. It's enough to bump up max_connections on the standby, and restart. > > Hmm... Petteri wants to solve the issue without changing anything > on the standby, according to his original post. So in his case, a fresh > backup is required. > > Of course my previous answer was confusing... I assume you mean the slave needs an updated copy of the master's config file changes, not an entire copy of the data directory. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [DOCS] [BUGS] BUG #7543: Invalid table alias: DELETE FROM table *
On Sun, Sep 16, 2012 at 11:58:06PM -0400, Tom Lane wrote: > barrybr...@sierracollege.edu writes: > > I sometime see my users delete all rows from a table using a command like > > this: > > > DELETE FROM customer *; > > > The question is: what is the star? Is it a table alias or an > > output_expression? > > Neither; it specifies to search the table and its inheritance children, > ie, the opposite of ONLY. This has been the default behavior (unless > you change the setting of sql_inheritance) for many years, so "*" has > largely fallen into disuse; but it's still accepted. > > However ... I went looking for documentation on this point, and I'm > darned if I can find any. There certainly used to be some, but > apparently somebody got over-eager about editing the docs to reflect > the modern default behavior. The "*" doesn't even appear in the syntax > summaries for most of the commands where it's allowed, which is flat > wrong --- anywhere you can write "ONLY tablename", it's valid to write > "tablename*" instead. > > So we have some docs work to do. Thanks for pointing it out. Is there any value to having * vs just not using ONLY? I am not sure documenting this is helping us, and it would add more clutter. Isn't this like how we don't document the old COPY syntax. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6528: pglesslog still referenced in docs, but no 9.1 support
On Tue, Sep 4, 2012 at 12:49:40PM -0500, Kevin Grittner wrote: > Bruce Momjian wrote: > > On Tue, Sep 4, 2012 at 12:11:53PM -0500, Kevin Grittner wrote: > > >> What do you think would be the right thing to do with it at this > >> point? > > > > Well, there should probably be a tar files with a README. What > > about adding this to /contrib? > > I would be happy to add it to contrib if people want it there. > There was some discussion of that when I wrote it, but then there > was a feeling that it would be uninteresting once people had > streaming replication. I'm skeptical that people will ever lose > interest in archiving WAL files, because of the not-uncommon need to > keep archival backups or restore to a "point in time" to recover > from a mangled table. As long as those needs are there, I suspect > that people will want to compress WAL files, including making > partially-filled files smaller. Agreed. Please move forward on the contrib idea. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6528: pglesslog still referenced in docs, but no 9.1 support
On Tue, Sep 4, 2012 at 12:11:53PM -0500, Kevin Grittner wrote: > Bruce Momjian wrote: > > On Mon, Apr 9, 2012 at 02:07:43PM -0500, Kevin Grittner wrote: > >> Bruce Momjian wrote: > >>> On Mon, Apr 09, 2012 at 03:37:09PM -0300, Alvaro Herrera wrote: > >>> > >>>> (Another related tool is clearxlogtail which zeroes areas from > >>>> WAL files when they are empty because of an early switch due to > >>>> archive timeout). > >>> > >>> Should we document that? > >> > >> Our shop has been using that since before pglesslog existed, and > >> it has continued to work across many major releases with no > >> change to source code because it doesn't get down to the level of > >> looking at the xlog records themselves, just the segment and > >> page-level structures. I should probably put packaging that up > >> as a proper extension and posting it to PGXN as another thing on > >> the list of things I'll do when I stumble over that pot of round > >> tuits at the end of the rainbow. Right now the source is on > >> pgfoundry. If anybody wants to do anything with it before I find > >> time, feel free. > > > > I was going to add a mention of this to our docs, but it seems > > there are no released files for the project: > > There is a single 175 line C source file to generate a stand-alone > executable filter. The source has not needed any changes since it > was finished over 4 years ago. Besides that C file, there is only a > make file. I wasn't sure what form of packaging would be easier > than just checking out the source from the SCM. > > What do you think would be the right thing to do with it at this > point? Well, there should probably be a tar files with a README. What about adding this to /contrib? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7507: pg_restore silently fails when restoring a db with the --create flag and no user.
On Mon, Aug 27, 2012 at 03:34:10PM +, i...@fuelforce.com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7507
> Logged by: Ian Nobile
> Email address: i...@fuelforce.com
> PostgreSQL version: 9.1.4
> Operating system: Mac OS X Version 10.8
> Description:
>
> Attempting to restore a db using pg_restore to a new database server using
> the --create and --dbname flags fails silently if the db owner username does
> not exist in the new db or the -O flag is not used.
> Command used:
> pg_restore --create --verbose --dbname=testserver
> /Volumes/USBLINKS1/hostname.testserver.net_postgres_database
Well, I tried this and it showed a proper error:
$ pg_restore --create --verbose -U yy --dbname=testserver /rtmp/xx
pg_restore: connecting to database for restore
pg_restore: [archiver (db)] connection to database "testserver" failed:
FATAL: role "yy" does not exist
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7482: lack of quotation marks in pg_env script
On Tue, Aug 7, 2012 at 12:15:14PM +, m.skrzypkow...@o2.pl wrote: > The following bug has been logged on the website: > > Bug reference: 7482 > Logged by: Maciej Skrzypkowski > Email address: m.skrzypkow...@o2.pl > PostgreSQL version: 9.1.4 > Operating system: Windows XP > Description: > > In main application directory, inside pg_env.bat script there is lack of > quotation marks for PGDATA and PGLOCALEDIR variables. Dave, has this been addressed? The community does not control pg_env.bat. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6712: PostgreSQL 9.2 beta2: alter table drop constraint does not work on inherited master table
On Mon, Jul 2, 2012 at 04:16:31PM +0530, Amit Kapila wrote: > > From: pgsql-bugs-ow...@postgresql.org > > [mailto:pgsql-bugs-ow...@postgresql.org] On Behalf Of > > miroslav.s...@fordfrog.com > > Sent: Saturday, June 30, 2012 4:28 PM > > The following bug has been logged on the website: > > > Bug reference: 6712 > > Logged by: Miroslav Šulc > > Email address: miroslav.s...@fordfrog.com > > PostgreSQL version: Unsupported/Unknown > > Operating system: Gentoo Linux > > Description: > > > here is the test case: > > > test=# create table test_constraints (id int, val1 varchar, val2 int, unique > > (val1, val2)); > > NOTICE: CREATE TABLE / UNIQUE will create implicit index > > "test_constraints_val1_val2_key" for table "test_constraints" > > CREATE TABLE > > test=# create table test_constraints_inh () inherits (test_constraints); > > CREATE TABLE > > test=# alter table only test_constraints drop constraint > > test_constraints_val1_val2_key; > > ERROR: constraint "test_constraints_val1_val2_key" of relation > > "test_constraints_inh" does not exist > > > > postgresql tries to drop the constraint even from descendant table though > > "only" is specified. > > > In function ATExecDropConstraint(), for the constarint > "test_constraints_val1_val2_key" con->connoinherit is false, > due to which it tries to drop the constrint from child table as well. > I have checked that from function index_constraint_create() when it calls > function CreateConstraintEntry(), the flag for noinherit passed is false. > I think this is the reason of failure for the same. I believe this if fixed in all current versions of Postgres. I tested 9.1, 9.2rc1, and 9.3. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6704: ALTER EXTENSION postgis SET SCHEMA leaves dangling relations
On Fri, Jun 22, 2012 at 10:37:10PM -0400, Tom Lane wrote: > j...@pgexperts.com writes: > > DROP and CREATE extension appear to work fine, but if you ALTER EXTENSION > > postgis SET SCHEMA foo, it leaves a few relations behind. > > What it seems to be leaving behind is indexes ... also relation rowtypes. > > A bit of looking shows that ALTER EXTENSION SET SCHEMA calls > AlterObjectNamespace_oid on the table. AlterObjectNamespace_oid > calls AlterRelationNamespaceInternal, and nothing else. In comparison, > ALTER TABLE SET SCHEMA (AlterTableNamespace) calls > AlterRelationNamespaceInternal and about four other things. I'm not > sure if this was broken before the last round of refactoring in this > area, but for sure it's broken now. Uh, did this get fixed? I can't find a commit related to the fix. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Upgrading vldb from 8.4.3 to 9.1.4
On Thu, Aug 30, 2012 at 02:48:51PM -0400, Freddie Burgess wrote: > We have successfully upgraded Postgresql from 8.4.3 to 9.1.4 in our > environment, but we have some questions/issues. > > Before the upgrade: > Postgres8 data location -- /usr/local/pgsql/data --> > /u01/fiber/postgres843/postgres_data > Postgres9 data location -- /opt/PostgreSQL/9.1/data --> > /u01/fiber/postgres_data > > After the upgrade: > Postgres9 (userdb schema) data location -- /usr/local/pgsql/data --> > /u01/fiber/postgres843/postgres_data > Postgres9 data location -- /opt/PostgreSQL/9.1/data --> > /u01/fiber/postgres_data > > We successfully moved all of the data files to /u01/fiber/postgres_data in > an effort to have all of the files located in the same area, but we are > currently stuck with regard to the two high level "data location" points: > /usr/local/pgsql/data and /opt/PostgreSQL/9.1/data, which are both soft > links to the fiber channel. > > Is there any way in Postgres to re-define the tablespace locations, or is > the only option available to us is to create new tablespaces w/the correct > directory paths and then move all of the objects (i.e. tables, indexes, > etc.) to the new tablespaces? This would take some time to script due to > the amount of objects we have, but it is doable. Are there any other > alternatives? > > How does Postgres moves the objects? Does It makes a copy of the object in > the new location and then deletes the original. If we Multiply by 5.4 TB of > data files, are we looking at an estimated completion time on the same order > of magnitude as the time it takes for us to perform the backup itself, which > takes approximately 5-7 days? > > Finally, when is it safe to remove the 8.4.3 objects/directory? You can move the data directory containing the default tablespace by just shutting down the server and moving the directory and restarting it. For user-defined tablespaces, you have to shut down the server and repoint the symlinks in pg_tblspc, and update the pg_tablespace.spclocation, which is removed in PG 9.2. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7511: 9.2: pg_stat_activity.procpid renamed unnecessarily
On Thu, Aug 30, 2012 at 04:34:18AM +, i...@avdd.co wrote: > The following bug has been logged on the website: > > Bug reference: 7511 > Logged by: Adrian Dries > Email address: i...@avdd.co > PostgreSQL version: Unsupported/Unknown > Operating system: any > Description: > > In trying 9.2 I find pg_stat_activity.procpid has been renamed to pid. > > http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=4f42b546fd87a80be30c53a0f2c897acb826ad52 > > It seems the premise of this is > > If we're going to rename current_query to query--what Scott's patch does > here--that will force all code using pg_stat_activity to be rewritten. This > seems like the perfect time to also change "procpid" to "pid", finally blow > away that wart. > > A premise that is untrue: changing a subset of columns on a relation does > not force "all code" using that relation to be rewritten: > > SELECT procpid FROM pg_stat_activity WHERE procpid <> pg_backend_pid() True. We were thinking more of tools that display all pg_stat_activity columns. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6639: Manual uses boldface where it says italic, and monospace where it says boldface
On Mon, May 14, 2012 at 09:41:59PM +, iain.dal...@gmail.com wrote: > The following bug has been logged on the website: > > Bug reference: 6639 > Logged by: Ian > Email address: iain.dal...@gmail.com > PostgreSQL version: 9.1.3 > Operating system: GNU/Linux > Description: > > The URL http://www.postgresql.org/docs/9.1/interactive/notation.html lays > out the notation used in the rest of the book, but it appears to be out of > date with the stylesheet, as the differences noted in the subject apply. Where are you seeing this? PDF, web version? An example? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6528: pglesslog still referenced in docs, but no 9.1 support
On Mon, Apr 9, 2012 at 02:07:43PM -0500, Kevin Grittner wrote: > Bruce Momjian wrote: > > On Mon, Apr 09, 2012 at 03:37:09PM -0300, Alvaro Herrera wrote: > > >> (Another related tool is clearxlogtail which zeroes areas from > >> WAL files when they are empty because of an early switch due to > >> archive timeout). > > > > Should we document that? > > Our shop has been using that since before pglesslog existed, and it > has continued to work across many major releases with no change to > source code because it doesn't get down to the level of looking at > the xlog records themselves, just the segment and page-level > structures. I should probably put packaging that up as a proper > extension and posting it to PGXN as another thing on the list of > things I'll do when I stumble over that pot of round tuits at the > end of the rainbow. Right now the source is on pgfoundry. If > anybody wants to do anything with it before I find time, feel free. I was going to add a mention of this to our docs, but it seems there are no released files for the project: http://pgfoundry.org/frs/?group_id=1000308 -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] [BUGS] BUG #6572: The example of SPI_execute is bogus
On Sun, Apr 15, 2012 at 12:29:39PM -0400, Tom Lane wrote: > Robert Haas writes: > > On Thu, Apr 5, 2012 at 2:39 AM, Hitoshi Harada wrote: > >> On Wed, Apr 4, 2012 at 8:00 AM, Tom Lane wrote: > >>> Given the lack of complaints since 9.0, maybe we should not fix this > >>> but just redefine the new behavior as being correct? But it seems > >>> mighty inconsistent that the tuple limit would apply if you have > >>> RETURNING but not when you don't. In any case, the ramifications > >>> are wider than one example in the SPI docs. > > >> To be honest, I was surprised when I found tcount parameter is said to > >> be applied to even INSERT. I believe people think that parameter is > >> to limit memory consumption when returning tuples thus it'd be applied > >> for only SELECT or DML with RETURNING. So I'm +1 for non-fix but > >> redefine the behavior. Who wants to limit the number of rows > >> processed inside the backend, from SPI? > > > Yeah. > > Okay, apparently nobody cares about RETURNING behaving differently from > non-RETURNING, so the consensus is to redefine the current behavior as > correct. That means what we need is to go through the docs and see what > places need to be updated (and, I guess, back-patch the changes to 9.0). > I will get to this if nobody else does, but not right away. Would someone make the doc change outlined above? Thanks. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6489: Alter table with composite type/table
On Wed, Mar 14, 2012 at 07:19:14PM +0100, Rikard Pavelic wrote: > On 13.3.2012. 20:49, Merlin Moncure wrote: > > I personally think it's an oversight. This was just discussed a > > couple of days ago here: > > http://postgresql.1045698.n5.nabble.com/Altering-a-table-with-a-rowtype-column-td5544844.html > > > > The server is blocking the alter-not-null-with-default because it's > > assuming that the default should be applied to dependent (foreign) > > tables implementing the type as a field. I think this assumption is > > totally bogus because composite types defaults get applied to the > > type, not to member fields and therefore a default has no meaning in > > that context. I think the TODO should read to relax the check > > essentially. > > > > merlin > > > > I agree. > TODO: alter table-type columns according to attribute type rules. > Enforce only TYPE features and ignore TABLE features when altering composite > table-types. > > While I'm making up TODO's, my favorite one: support recursive types. Should we add this TODO? I am confused by the text above though. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6412: psql & fe-connect truncate passwords
On Sat, Jan 28, 2012 at 01:47:04PM -0500, Tom Lane wrote: > Euler Taveira de Oliveira writes: > > I don't see it as a bug but a limitation. Why do you need such a long > > password? > > Yeah, I think the reason we're not too consistent about this is that > nobody ever imagined that limits of 100 bytes or more would pose an > issue in practice. What's the use-case for passwords longer than > that? Thanks for all the feedback. I know it is a pain for me to re-ask these questions, but it allows us to know that these issues have gotten sufficient thought. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6412: psql & fe-connect truncate passwords
*t = buf,
> + char *t = calloc(1,sizeof(char)),
> *ret,
> *p1,
> *p2;
> int len;
>
> - if (fgets(buf, sizeof(buf), fp) == NULL)
> - break;
>
> - len = strlen(buf);
> + do
> + {
> + if ( fgets(buf, LINELEN, fp) == NULL)
> + break;
> + t = realloc(t, strlen(t)+1+strlen(buf));
> + /* Out of memory? */
> + if( !t )
> + return NULL;
> + strcat(t, buf);
> + len = strlen(t);
> + } while (strlen(buf) > 0 && t[len-1] != '\n');
> +
> if (len == 0)
> continue;
>
> /* Remove trailing newline */
> - if (buf[len - 1] == '\n')
> - buf[len - 1] = 0;
> + while ( len > 0 && (t[len-1] == '\n' || t[len-1] == '\r'))
> + t[--len] = 0;
>
> if ((t = pwdfMatchesString(t, hostname)) == NULL ||
> (t = pwdfMatchesString(t, port)) == NULL ||
> diff --git a/src/port/sprompt.c b/src/port/sprompt.c
> index 7baa26e..aafec28 100644
> --- a/src/port/sprompt.c
> +++ b/src/port/sprompt.c
> @@ -38,7 +38,10 @@ char *
> simple_prompt(const char *prompt, int maxlen, bool echo)
> {
> int length;
> + int buflen;
> + int bufsize = 1024;
> char *destination;
> + char buf[bufsize];
> FILE *termin,
> *termout;
>
> @@ -52,7 +55,11 @@ simple_prompt(const char *prompt, int maxlen, bool echo)
> #endif
> #endif
>
> - destination = (char *) malloc(maxlen + 1);
> + if (maxlen > 0) {
> + destination = (char *) calloc(1, sizeof(char));
> + } else {
> + destination = (char *) malloc((maxlen + 1) * sizeof(char));
> + }
> if (!destination)
> return NULL;
>
> @@ -108,21 +115,34 @@ simple_prompt(const char *prompt, int maxlen, bool echo)
> fflush(termout);
> }
>
> - if (fgets(destination, maxlen + 1, termin) == NULL)
> - destination[0] = '\0';
> -
> - length = strlen(destination);
> - if (length > 0 && destination[length - 1] != '\n')
> - {
> - /* eat rest of the line */
> - charbuf[128];
> - int buflen;
> + if (maxlen > 0) {
> + if (fgets(destination, maxlen + 1, termin) == NULL)
> + destination[0] = '\0';
>
> + length = strlen(destination);
> + if (length > 0 && destination[length - 1] != '\n')
> + {
> + /* eat rest of the line */
> + do
> + {
> + if (fgets(buf, bufsize, termin) == NULL)
> + break;
> + buflen = strlen(buf);
> + } while (buflen > 0 && buf[buflen - 1] != '\n');
> + }
> +
> + } else {
> do
> {
> - if (fgets(buf, sizeof(buf), termin) == NULL)
> + if (fgets(buf, bufsize, termin) == NULL)
> break;
> buflen = strlen(buf);
> + destination = realloc( destination,
> strlen(destination)+1+buflen );
> + /* Out of memory ? */
> + if( !destination )
> + return NULL;
> + strcat( destination, buf );
> + length = strlen(destination);
> } while (buflen > 0 && buf[buflen - 1] != '\n');
> }
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6401: IS DISTINCT FROM improperly compares geomoetric datatypes
Is this a TODO? --- On Thu, Jan 19, 2012 at 10:39:42AM -0500, Tom Lane wrote: > Alvaro Herrera writes: > > Excerpts from Heikki Linnakangas's message of jue ene 19 07:25:36 -0300 > > 2012: > >> Frankly that's such a rare corner case that I'm not very enthusiastic > >> about fixing it. One idea would be to look up the type's b-tree sort > >> operators, and pick the equality operator from there. But point datatype > >> doesn't have b-tree sort operators, either, so it wouldn't help in this > >> case. > > > It doesn't have a hash opclass either, which could be used as a fallback > > in case there's no btree. Point cannot obviously have a btree opclass > > (no inequalities), but a hash one seems possible. > > > I think the use case of IS NOT DISTINCT FROM for rowtypes in triggers is > > a valid one. > > Note that IS [NOT] DISTINCT is not the only place that assumes that it > should use an operator named "=". There's also scalar IN, the simple > form of CASE, and possibly some others that I forget at the moment. > IMO, if we're going to change the semantics of any of these, we should > do them all together. > > This is something I've kinda wanted to do for a long time, but never > gotten around to. We've managed to clean up hard-wired assumptions > about operator names in a lot of other places, but these syntactic > constructs still do it by name. > > One argument against changing it is that arguably doing so would violate > the letter of the SQL standard. For example, I observe that SQL defines > the IN construct thus: > > The expression > > RVC IN IPV > > is equivalent to > > RVC = ANY IPV > > (SQL99 8.4 syntax rule 4). The word "equality" appears > nowhere in the definition of IN. Thus, if we take "X IN (Y,Z,Q)" and > implement it with some operator not named "=", we have not done what > the spec clearly says to do. Now you can make the case that we'd be > implementing the spirit rather than the letter of the spec, but that's > a rather shaky case to have to make. > > The same is true for simple CASE: > > c) The is equivalent to a > in which each specifies acondition> of the form "CO=WO". > > with absolutely no hint that equality is what the "=" symbol is supposed > to get you. And in 8.13 we have > > Case: > i) "X IS DISTINCT FROM Y" is false if either: > 1) X and Y are the null value, or > 2) X = Y according to Subclause 8.2, "predicate>". > ii) Otherwise, "X IS DISTINCT FROM Y" is true. > > which at least suggests that what's wanted is equality, but they're > still defining it in terms of an operator named "=" (and AFAICS > subclause 8.2 doesn't address the possibility that "X=Y" could mean > something other than the common idea of equality). > > So on the whole, it might be better to just provide an operator named > "=" for point, and not open up the can of worms about whether these > constructs should use some other rule for deciding which operator to > compare with. > > regards, tom lane > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] [ADMIN] Repeatable crash in pg_dump (with -d2 info)
On Tue, Jan 17, 2012 at 04:46:50PM -0500, David Schnur wrote: > I finally had time to test this further on a variety of systems, and was > unable > to reproduce on any non-Windows platform. The dump even works fine on Windows > XP; just not Windows 7. > > This prompted me to do a little more research, and this time I found this > thread from Sept. 2011: > > http://postgresql.1045698.n5.nabble.com/ > BUG-6233-pg-dump-hangs-with-Access-Violation-C005-td4851598.html > > From Tom Lane in the above thread: > > > Hmm. I can see how that would happen if you're using one of the Windows > environments wherein malloc's done inside libpq have to be free'd inside > libpq. (The PQExpBuffer support code is in libpq...) > > > > However, the flaw in that explanation is that it would basically mean > pg_dump doesn't work at all on Windows, at least not if you have any > user-defined functions, and probably some other cases too because there > seem to be multiple instances of the dubious coding. It's a bit hard to > believe that nobody's noticed that before. > > > This appears to describe exactly the issue I'm encountering, and my build is > in > fact linked against the static runtime. I guess the reason this hasn't come > up > sooner is because most Windows users either use the 'official' binaries rather > than compiling from source, or link against the dynamic runtime. > > Is this something I could expect to be fixed in the near future, or is it > enough of an edge case that I should come up with some solution or work-around > on my own? Thanks, Late reply, but I don't see any way we could fix this easily. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] [PATCH] Prevent hanging on unreachable hosts on startup
On Thu, Jan 5, 2012 at 09:23:55AM -0500, Ryan Kelly wrote:
> On Wed, Jan 04, 2012 at 09:36:57PM -0500, Tom Lane wrote:
> > "Ryan P. Kelly" writes:
> > > The signal handler installed by setup_cancel_handler() will ignore
> > > attempts to exit psql should a host be unreachable.
> >
> > Hm. That may be worth doing something about, but the proposed patch
> > seems more like a quick-and-dirty hack than a solution. The main
> > thing I don't like about it is that if we care about this scenario
> > during initial startup, we should also care about it during a \c
> > command, but just delaying the installation of the signal handler
> > won't fix that case.
> Sure, but do you still think moving the signal handler down is correct?
> I've attached another patch that handles interrupting \c. I also noticed
> there appears to be what looks like duplication of code in startup.c and
> command.c in main() and do_connect() respectively. I'm wondering if I
> they should be made to share the same code which (with my patch) would
> then mean the signal handler could be left where it was.
>
> > More generally, what if the server becomes unreachable mid-session?
> > I'm not sure how much there is to be done about that case, since
> > there's probably no good way to distinguish it from a query that
> > takes a really long time. But if we're going to think about this,
> > we might as well think about all the cases.
> Well in this case we can still interrupt the query, no? This will drop
> us back into the prompt, where we can safely \q.
Tom, can you comment on this patch because you commented on the previous
version? Thanks.
---
> >From 8f9d0b5088021d944aceac65a96d7bd0c24aa0c6 Mon Sep 17 00:00:00 2001
> From: "Ryan P. Kelly"
> Date: Thu, 5 Jan 2012 09:13:38 -0500
> Subject: [PATCH] Allow interrupting hanging \c connection attempts
>
> ---
> src/bin/psql/command.c | 17 +
> 1 files changed, 13 insertions(+), 4 deletions(-)
>
> diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
> index 69fac83..845705d 100644
> --- a/src/bin/psql/command.c
> +++ b/src/bin/psql/command.c
> @@ -1516,7 +1516,7 @@ static bool
> do_connect(char *dbname, char *user, char *host, char *port)
> {
> PGconn *o_conn = pset.db,
> -*n_conn;
> +*n_conn = NULL;
> char *password = NULL;
>
> if (!dbname)
> @@ -1570,7 +1570,13 @@ do_connect(char *dbname, char *user, char *host, char
> *port)
> keywords[7] = NULL;
> values[7] = NULL;
>
> - n_conn = PQconnectdbParams(keywords, values, true);
> + if (sigsetjmp(sigint_interrupt_jmp, 1) != 0) {
> + /* got here with longjmp */
> + } else {
> + sigint_interrupt_enabled = true;
> + n_conn = PQconnectdbParams(keywords, values, true);
> + sigint_interrupt_enabled = false;
> + }
>
> free(keywords);
> free(values);
> @@ -1600,7 +1606,8 @@ do_connect(char *dbname, char *user, char *host, char
> *port)
>*/
> if (pset.cur_cmd_interactive)
> {
> - psql_error("%s", PQerrorMessage(n_conn));
> + if (n_conn)
> + psql_error("%s", PQerrorMessage(n_conn));
>
> /* pset.db is left unmodified */
> if (o_conn)
> @@ -1608,7 +1615,9 @@ do_connect(char *dbname, char *user, char *host, char
> *port)
> }
> else
> {
> - psql_error("\\connect: %s", PQerrorMessage(n_conn));
> + if (n_conn)
> + psql_error("\\connect: %s",
> PQerrorMessage(n_conn));
> +
> if (o_conn)
> {
> PQfinish(o_conn);
> --
> 1.7.7.4
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6372: Error while creating database with fsync parameter as on incase of CIFS
On Mon, Jan 2, 2012 at 04:00:19PM -0500, Tom Lane wrote: > Alvaro Herrera writes: > > Excerpts from Tom Lane's message of lun ene 02 17:28:33 -0300 2012: > >> it seems like EINVAL is a considerably more reasonable thing to return > >> than EBADF, if the filesystem is trying to tell you that it won't fsync > >> a directory. So I'm a bit surprised this question hasn't come up for > >> other filesystems. > > > Probably because other filesystems do allow you to fsync directories. > > In fact for some cases they _require_ it ... remember the fiasco when > > MTA writers were told that they needed to fsync their queue dirs in > > order for all queued email to persist? > > Yeah, the long and the short of it is that if the filesystem won't > accept an fsync on a directory, we have to assume that it doesn't need > it and will manage metadata persistence safely without prodding. > > The only real question here is whether an EINVAL could mean something > besides "fsync on directory is not accepted". If there are any > scenarios where it represents a transient/fixable error, then we'd > want to report it. It's far from clear to me that there are any > though. What it could mean in general is not at issue, because we > know the target is a directory that we just created moments before. I assume this never got resolved. Should it be changed to ignore EINVAL? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6067: In PL/pgsql, EXISTS(SELECT ... INTO...) fails
On Wed, Nov 30, 2011 at 03:36:11PM -0500, Robert Haas wrote: > On Tue, Nov 29, 2011 at 9:32 PM, Bruce Momjian wrote: > > Tom Lane wrote: > >> "David Fetter" writes: > >> > IF EXISTS (SELECT 1 INTO STRICT i) THEN > >> > RAISE NOTICE '%', a; > >> > END IF; > >> > >> Umm ... are you just complaining that the error message isn't very > >> helpful, or are you actually expecting that to do something useful? > >> If the latter, what exactly? I'm particularly confused by your use > >> of the STRICT option here, because if we did support that, I would > >> expect the STRICT to throw an error if there were not exactly one > >> matching row, making the EXISTS test 100% pointless. > >> > >> But the short answer is that we don't support INTO in sub-selects, > >> and in general I doubt that we ever will, since in most cases the > >> behavior wouldn't be very well-defined. It might be worth a TODO > >> to provide a better error message than "syntax error", though. > > > > Is it worth documenting, fixing, or adding this to the TODO list? > > At most I would say we could try to improve the error message. I researched this and it seems to complex to improve the error message. I am afraid it would have to bleed into the main backend parser. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Cannot dump 8.4.8 database using later versions
I don't think we ever addressed this, but since we have had minimal complaints about it, I guess we are OK. --- On Tue, Nov 15, 2011 at 10:04:57PM -0500, Tom Lane wrote: > Robert Haas writes: > > On Tue, Nov 15, 2011 at 5:16 AM, Heikki Linnakangas > > wrote: > >> NEW used to be a reserved keyword, but it's not so in 9.0 anymore. So 9.0 > >> pg_dump thinks it doesn't need to be quoted. > > > Why isn't it correct? > > It's correct to not quote it in pg_dump's output (since we make no > promises that such output would load into a pre-9.0 server anyway). > The problem is that it needs to be quoted in commands that pg_dump > sends back to the 8.4 server. Example: > > psql (8.4.9) > You are now connected to database "db84". > db84=# create table "new"( f1 int, "new" text); > > ... pg_dump with newer pg_dump ... > > pg_dump: SQL command failed > pg_dump: Error message from server: ERROR: syntax error at or near "new" > LINE 1: COPY public.new (f1, new) TO stdout; > ^ > pg_dump: The command was: COPY public.new (f1, new) TO stdout; > > The least painful solution might be to always quote *every* identifier > in commands sent to the source server, since we don't especially care > how nice-looking those are. > > regards, tom lane -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] 'pg_ctl restart' confused about pathname to postgresql.conf
I have applied the attached doc patch to document the problem with relative paths and pg_ctl restart. --- On Sun, Oct 23, 2011 at 08:49:25PM -0400, Josh Kupershmidt wrote: > On Sat, Oct 22, 2011 at 12:13 PM, Tom Lane wrote: > > I think the reason it has a problem is that this is what's left in > > postmaster.opts: > > > > /home/tgl/pgsql/bin/postgres "-D" "baz" > > > > (which is an accurate representation of the command line from startup) > > and that -D switch gets fed to the postmaster as-is during restart. > > I see. > > > By and large, I would not recommend using a relative pathname to start > > the postmaster, unless you plan to start it from the same working > > directory every time. > > Well, now I know. But that really seems like an annoying and arbitrary > restriction, not to mention not being documented anywhere AFAICT. > > (I came upon this problem because I often set up servers with > binaries, libraries, and $PGDATA all tucked away under > /home/postgres/, and it seemed natural to use a relative pathname as > my data directory argument to pg_ctl since my working directory will > usually be /home/postgres/ when I'm poking at the server.) > > > We could possibly avoid this by having pg_ctl try to absolute-ify the -D > > setting during postmaster start, but I'm not convinced it's worth the > > trouble, or even that it's appropriate for pg_ctl to editorialize on the > > user's choice of absolute vs relative path. > > I don't want to bikeshed on the mechanics of how exactly this should > work, but it doesn't seem like it should be so hard to get this to > DWIM. In the example I posted, the last step which fails is basically: > > pg_ctl -D /tmp/foo/bar/baz/ restart > > and it just seems totally broken for that to not work: pg_ctl knows > exactly which data directory the user means when invoked here. Plus, > these steps would work fine instead at that point: > > pg_ctl -D /tmp/foo/bar/baz/ stop > pg_ctl -D /tmp/foo/bar/baz/ start > > and I was under the impression (supported by the pg_ctl doc page, > which claims "restart mode effectively executes a stop followed by a > start") that these sequences should be equivalent. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/ref/pg_ctl-ref.sgml b/doc/src/sgml/ref/pg_ctl-ref.sgml new file mode 100644 index 7a4c2be..3107514 *** a/doc/src/sgml/ref/pg_ctl-ref.sgml --- b/doc/src/sgml/ref/pg_ctl-ref.sgml *** PostgreSQL documentation *** 188,194 restart mode effectively executes a stop followed by a start. This allows changing the postgres !command-line options. --- 188,196 restart mode effectively executes a stop followed by a start. This allows changing the postgres !command-line options. restart might fail if !relative paths specified were specified on the command-line during !server start. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] psql doesn't reuse -p after backend fail
On Wed, Sep 14, 2011 at 10:52:50PM -0500, Robert Haas wrote:
> On Thu, Sep 8, 2011 at 5:09 AM, Peter Eisentraut wrote:
> > On tis, 2011-09-06 at 17:12 +0200, hubert depesz lubaczewski wrote:
> >> On Mon, Sep 05, 2011 at 02:27:23PM -0400, Tom Lane wrote:
> >> > It's not just the port, it's all the connection parameters ---
> >> > do_connect relies on the PGconn object to remember those, and in this
> >> > case there no longer is a PGconn object.
> >> >
> >> > We could have psql keep that information separately, but I'm not sure
> >> > it's really worth the trouble.
> >>
> >> well, I think it's definitely worth the trouble. If I had datbaase
> >> standing at 5432, it would connect to it, and then I could mistakenly
> >> ran commands to wrong database.
> >> this is clearly not a good thing.
> >
> > Perhaps just prevent \connect without argument if the information is no
> > longer available.
>
> I think it'd be worth actually having psql maintain the information
> separately from the PGconn... but if nobody feels motivated to go do
> that, doing at least this much would remove the foot-gun. So +1 for
> that.
OK, I have applied the attached, applied patch to do as you suggest.
Here are examples:
!> SELECT * FROM mytable WHERE to_ascii(convert_to(mytext, 'latin1'),
'latin1')
-> = to_ascii(convert_to('nicetry', 'latin1'), 'latin1');
You are currently not connected to a database.
!> \c
All connection parameters must be supplied because no database
connection exists
!> \q
$ psql -p 5433 test
psql (9.3devel)
Type "help" for help.
test=> \c
You are now connected to database "test" as user "postgres".
test=> \q
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
new file mode 100644
index 6ead800..2cd3ab4
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*** do_connect(char *dbname, char *user, cha
*** 1512,1517
--- 1512,1523
*n_conn;
char *password = NULL;
+ if (!o_conn && (!dbname || !user || !host || !port))
+ {
+ fputs(_("All connection parameters must be supplied because no database connection exists\n"), stderr);
+ return false;
+ }
+
if (!dbname)
dbname = PQdb(o_conn);
if (!user)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6178: date_trunc : interval units "week" not supported contradicts documentation
On Thu, Aug 25, 2011 at 07:05:34PM +, Noah Hamerslough wrote:
>
> The following bug has been logged online:
>
> Bug reference: 6178
> Logged by: Noah Hamerslough
> Email address: n...@pcc.com
> PostgreSQL version: 8.4
> Operating system: Windows Vista
> Description:date_trunc : interval units "week" not supported
> contradicts documentation
> Details:
>
> http://www.postgresql.org/docs/8.4/static/functions-datetime.html#FUNCTIONS-
> DATETIME-TRUNC
>
> The documentation for date_trunc('field', source) lists 'week' in the as a
> valid value for 'field' However, if the source is an interval, 'week' is not
> supported.
>
> select date_trunc('week', '1 month 15 days'::interval);
>
> ERROR: interval units "week" not supported
> SQL state: 0A000
>
> Either 'week' should be supported or the documentation should be updated to
> reflect that it is not.
Turns out the reason we don't support this is because there are usually
a fractional number of weeks in a month, so there is no good way to do
this for intervals. I have applied the attached patch to PG 9.3 which
will explain why this is not supported. I saw this as better than a
documentation mention.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
new file mode 100644
index 8593b6b..2adc178
*** a/src/backend/utils/adt/timestamp.c
--- b/src/backend/utils/adt/timestamp.c
*** interval_trunc(PG_FUNCTION_ARGS)
*** 3710,3719
break;
default:
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("interval units \"%s\" not supported",
! lowunits)));
}
if (tm2interval(tm, fsec, result) != 0)
--- 3710,3726
break;
default:
! if (val == DTK_WEEK)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("interval units \"%s\" not supported "
! "because months usually have fractional weeks",
! lowunits)));
! else
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("interval units \"%s\" not supported",
! lowunits)));
}
if (tm2interval(tm, fsec, result) != 0)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6165: documentation bug in plpgsql-declarations.html and plpgsql-statements.html (or plpgsql parser bug)
On Mon, Aug 22, 2011 at 04:17:04PM -0400, Tom Lane wrote:
> Robert Haas writes:
> > On Wed, Aug 17, 2011 at 1:21 AM, raf wrote:
> >> so, there is either a plpgsql parser bug that treats the sql equality
> >> operator as the plpgsql assignment operator, or "=" is an undocumented
> >> alternative to the documented plpgsql assignment operator (":=").
>
> > I think it's the latter.
>
> It's definitely intentional, not a bug, so far as the source code is
> concerned:
>
> assign_operator : '='
> | COLON_EQUALS
> ;
>
> > I have a vague recollection that we might
> > have left that undocumented on purpose, but I'm not actually sure why
> > we support it in the first place.
>
> I think it's legacy at this point. We have discussed before whether to
> document it, and IIRC the general feeling was "if we do document it,
> we'll never be able to get rid of it". Whether we could get rid of it
> now (instead of documenting it) was not seriously discussed. I've seen
> at least a few people saying that they do rely on it ...
I think the question is whether '=' is used enough that we have to
mention that it is a non-standard extension that might be removed
someday, or something.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6162: initdb : Windows username isn't escaped
On Sun, Aug 14, 2011 at 11:25:22AM +, Emmanuel Guyot wrote:
>
> The following bug has been logged online:
>
> Bug reference: 6162
> Logged by: Emmanuel Guyot
> Email address: emmanuel.gu...@gmail.com
> PostgreSQL version: 8.2.3
> Operating system: Windows 7
> Description:initdb : Windows username isn't escaped
> Details:
>
> Initdb crashes when I use it with a windows username that has a quote inside
> (e.g. : L'élixir)
>
> Here is the error reported :
> initialisation des droits sur les objets internes... 2010-05-14 17:01:25.434
> FATAL: syntax error at or near "élixir" at character 44
> 2010-05-14 17:01:25.434 STATEMENT: UPDATE pg_class SET relacl =
> E'{"=r/\\"L'élixir\\""}' WHERE relkind IN ('r', 'v', 'S') AND relacl IS
> NULL;
>
> If I force the username as an option with the escaped value L\'élixir the
> initdb works fine, but I can't start the server. So I can't find a
> workaround.
>
> I've watched the newer sources of initdb and I can't see any change for this
> bug.
I have fixed the problem with the attached, applied patch, which will
appear in Postgres 9.3. The problem was that single-quotes in usernames
were not properly escaped by initdb.
Also, I have improved the pg_hba.conf documentation, and added an assert
to catch future breakage.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml
new file mode 100644
index 679c40a..9771394
*** a/doc/src/sgml/client-auth.sgml
--- b/doc/src/sgml/client-auth.sgml
***
*** 80,86
Records cannot be continued across lines.
A record is made
up of a number of fields which are separated by spaces and/or tabs.
!Fields can contain white space if the field value is quoted.
Quoting one of the keywords in a database, user, or address field (e.g.,
all or replication) makes the word lose its special
character, and just match a database, user, or host with that name.
--- 80,86
Records cannot be continued across lines.
A record is made
up of a number of fields which are separated by spaces and/or tabs.
!Fields can contain white space if the field value is double-quoted.
Quoting one of the keywords in a database, user, or address field (e.g.,
all or replication) makes the word lose its special
character, and just match a database, user, or host with that name.
diff --git a/src/backend/parser/scansup.c b/src/backend/parser/scansup.c
new file mode 100644
index 6101457..b8e2f71
*** a/src/backend/parser/scansup.c
--- b/src/backend/parser/scansup.c
*** scanstr(const char *s)
*** 56,61
--- 56,63
* appear in pairs, so there should be another character.
*/
i++;
+ /* The bootstrap parser is not as smart, so check here. */
+ Assert(s[i] == '\'');
newStr[j] = s[i];
}
else if (s[i] == '\\')
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
new file mode 100644
index 132ad0f..a53760a
*** a/src/bin/initdb/initdb.c
--- b/src/bin/initdb/initdb.c
*** bootstrap_template1(void)
*** 1395,1401
bki_lines = replace_token(bki_lines, "FLOAT8PASSBYVAL",
FLOAT8PASSBYVAL ? "true" : "false");
! bki_lines = replace_token(bki_lines, "POSTGRES", username);
bki_lines = replace_token(bki_lines, "ENCODING", encodingid);
--- 1395,1401
bki_lines = replace_token(bki_lines, "FLOAT8PASSBYVAL",
FLOAT8PASSBYVAL ? "true" : "false");
! bki_lines = replace_token(bki_lines, "POSTGRES", escape_quotes(username));
bki_lines = replace_token(bki_lines, "ENCODING", encodingid);
*** setup_privileges(void)
*** 2043,2050
PG_CMD_OPEN;
! priv_lines = replace_token(privileges_setup,
! "$POSTGRES_SUPERUSERNAME", username);
for (line = priv_lines; *line != NULL; line++)
PG_CMD_PUTS(*line);
--- 2043,2050
PG_CMD_OPEN;
! priv_lines = replace_token(privileges_setup, "$POSTGRES_SUPERUSERNAME",
! escape_quotes(username));
for (line = priv_lines; *line != NULL; line++)
PG_CMD_PUTS(*line);
*** main(int argc, char *argv[])
*** 3056,3062
canonicalize_path(pg_data);
#ifdef WIN32
-
/*
* Before we execute another program, make sure that we are running with a
* restricted token. If not, re-execute ourselves with one.
--- 3056,3061
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7494: WAL replay speed depends heavily on the shared_buffers size
On Wed, Aug 15, 2012 at 10:10:42AM +, val...@gmail.com wrote: > The following bug has been logged on the website: > > Bug reference: 7494 > Logged by: Valentine Gogichashvili > Email address: val...@gmail.com > PostgreSQL version: 9.0.7 > Operating system: Linux version 2.6.32-5-amd64 (Debian 2.6.32-41) > Description: > > We are experiencing strange(?) behavior on the replication slave machines. > The master machine has a very heavy update load, where many processes are > updating lots of data. It generates up to 30GB of WAL files per hour. > Normally it is not a problem for the slave machines to replay this amount of > WAL files on time and keep on with the master. But at some moments, the > slaves are “hanging” with 100% CPU usage on the WAL replay process and 3% > IOWait, needing up to 30 seconds to process one WAL file. If this tipping > point is reached, then a huge WAL replication lag is building up quite fast, > that also leads to overfill of the XLOG directory on the slave machines, as > the WAL receiver is putting the WAL files it gets via streaming replication > the XLOG directory (that, in many cases are quite a limited size separate > disk partition). > > What we noticed also, is that reducing shared_buffers parameter from our > normal 20-32 GB for the slave machines, to 2 GB increases the speed of WAL > replay dramatically. After restart of the slave machine with much lower > shared_buffers values, the replay becomes up to 10-20 times faster. > > On the attached graph, there is a typical graph of WAL replication delay for > one of the slaves. > > In that graph small (up to 6GB) replication delay peaks during the night are > caused by some long running transactions, stopping WAL replay on this slave, > to prevent replication collisions. But the last, big peaks are sometimes > start because of that waiting for a long running transaction on the slave, > but then they are growing as described above. > > I know, that there is only one process that replays data, generated by many > threads on master machine. But why does the replay performance depend so > much on the shared_buffers parameter and can it be optimized? We warn against making shared buffers > 8GB, and this is perhaps another good reason. The problem is probably due to the shared buffers filling up with lots of dirty data, and the kernel being unable to contain all the data coming during a checkpoint. It is also possible that the buffer management overhead is just too high for that many buffers. It is also possible that Postgres 9.1 or the coming 9.2 would do better in this regard. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6150: commit_delay should have unit ms
On Tue, Aug 14, 2012 at 05:26:39PM -0400, Tom Lane wrote: > Bruce Momjian writes: > > Opps, turns out the units are microseconds (as pointed out to me by > > Magnus), and we don't have a microsecond designation in that file, so I > > reverted that and just added a C comment about why it has no units. > > Is it worth adding support for microseconds in there? Not sure. > > Technically it wouldn't be very hard, but I seem to recall this was > discussed before and we were worried about whether people would be > confused about what "ms" means. Don't know that "us" would be > universally recognized, either. Yes, I do remember the discussion. Because commit_delay is so rarely used, and the only one that uses usec units by default, it didn't seem to make sense to add documentation for an additional unit. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] ERROR - Upgrading Postgresql 8.4.3 to Postgressql 9.1.4
On Tue, Aug 14, 2012 at 04:27:45PM -0400, Freddie Burgess wrote:
> We are trying to upgrade our production postgres database instance that is
> approximately 4TB in size and it contains various table partition structures.;
> a separate tablespace for each partition. We were initially successful in
> performing the upgrade using pg_upgrade with both the link and copy options in
> our development environment, but that environment has a different hardware
> configuration {DAS storage and centOS operating system and dedicated server}
> than what is configured in production.
>
>
>
> When we attempted to upgrade using pg_upgrade with the link option on our
> production database. Our Production database is configured with RHEL 5.4, NAS
> storage; configured utilizing a symlink that references the file system on the
> network, and VMWare.
>
>
>
> We ran the pg_upgrade procedures, and it completed to quickly (less than 10
> mins)
That is normal for link mode --- it is using hard links for the data
files.
> - pg_upgrade (using the link option) is supposed to merge the legacy (8.4.3)
> DB
> cluster with the new, empty (9.1.4) cluster.
Yes.
> - Both databases clusters were being identified as 8.4.3 clusters AFTER the
> upgrade. This was strange, we do not have an explanation. It acted as if the
> upgrade downgraded the 9.1.4 cluster to 8.4.3.
That is very odd, and I have never heard of that problem before.
> - At completion, the DB files were unusable. They would not start under 9.1.4
> or 8.4.3.
>
>
>
> Could our production server’s hardware configuration possibility be the
> culprit
> responsible for this upgrade failure?
I can't see how, unless you were somehow using the NAS in a way that
didn't allow hard links, but I am unclear how that would happen, and
pg_upgrade checks for that.
I guess the big question is how is production different from testing,
and how does the new server identify itself as 8.4 if you can't connect
to it. Something is obviously majorly messed up.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6150: commit_delay should have unit ms
On Tue, Aug 14, 2012 at 04:18:40PM -0400, Bruce Momjian wrote: > On Thu, Aug 4, 2011 at 09:30:35PM +, Christoph Anton Mitterer wrote: > > > > The following bug has been logged online: > > > > Bug reference: 6150 > > Logged by: Christoph Anton Mitterer > > Email address: cales...@scientia.net > > PostgreSQL version: 0.9 > > Operating system: Debian sid > > Description:commit_delay should have unit ms > > Details: > > > > Hi. > > > > AFAIK form the documentation, the setting commit_delay, should have the base > > unit "ms". > > > > However, when doing something like > > $ psql -c 'SELECT name,vartype,unit FROM pg_settings;' | grep commit_delay > > I get: > > could not change directory to "/root/test" > > commit_delay| integer | > > > > No unit. > > Yes, you are correct. The attached, applied patch for Postgres 9.3 will > properly display units. I checked all the other variables and they all > had proper units. > > I also removed an unnecessary units designation in > postgresql.conf.sample for a zero value --- if we want to put units on > zero values, we should do it consistently in a separate patch. Opps, turns out the units are microseconds (as pointed out to me by Magnus), and we don't have a microsecond designation in that file, so I reverted that and just added a C comment about why it has no units. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6150: commit_delay should have unit ms
On Thu, Aug 4, 2011 at 09:30:35PM +, Christoph Anton Mitterer wrote:
>
> The following bug has been logged online:
>
> Bug reference: 6150
> Logged by: Christoph Anton Mitterer
> Email address: cales...@scientia.net
> PostgreSQL version: 0.9
> Operating system: Debian sid
> Description:commit_delay should have unit ms
> Details:
>
> Hi.
>
> AFAIK form the documentation, the setting commit_delay, should have the base
> unit "ms".
>
> However, when doing something like
> $ psql -c 'SELECT name,vartype,unit FROM pg_settings;' | grep commit_delay
> I get:
> could not change directory to "/root/test"
> commit_delay| integer |
>
> No unit.
Yes, you are correct. The attached, applied patch for Postgres 9.3 will
properly display units. I checked all the other variables and they all
had proper units.
I also removed an unnecessary units designation in
postgresql.conf.sample for a zero value --- if we want to put units on
zero values, we should do it consistently in a separate patch.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
new file mode 100644
index 80e5aa1..a1d303b
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
*** static struct config_int ConfigureNamesI
*** 2033,2039
{"commit_delay", PGC_USERSET, WAL_SETTINGS,
gettext_noop("Sets the delay in microseconds between transaction commit and "
"flushing WAL to disk."),
! NULL
},
&CommitDelay,
0, 0, 10,
--- 2033,2040
{"commit_delay", PGC_USERSET, WAL_SETTINGS,
gettext_noop("Sets the delay in microseconds between transaction commit and "
"flushing WAL to disk."),
! NULL,
! GUC_UNIT_MS
},
&CommitDelay,
0, 0, 10,
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
new file mode 100644
index c24afb0..adcbcf6
*** a/src/backend/utils/misc/postgresql.conf.sample
--- b/src/backend/utils/misc/postgresql.conf.sample
***
*** 137,143
# - Cost-Based Vacuum Delay -
! #vacuum_cost_delay = 0ms # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-1 credits
#vacuum_cost_page_miss = 10 # 0-1 credits
#vacuum_cost_page_dirty = 20 # 0-1 credits
--- 137,143
# - Cost-Based Vacuum Delay -
! #vacuum_cost_delay = 0 # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-1 credits
#vacuum_cost_page_miss = 10 # 0-1 credits
#vacuum_cost_page_dirty = 20 # 0-1 credits
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6126: CC parameter in to_char() behaves incorrectly
On Mon, Aug 6, 2012 at 03:20:18PM -0400, Bruce Momjian wrote:
> On Sat, Aug 4, 2012 at 10:34:14AM -0400, Bruce Momjian wrote:
> > > I am thinking it is too late to apply this for 9.2 because users might
> > > have already tested their applications, though I doubt many are using BC
> > > dates. Feedback?
> >
> > There is never just one bug in formatting.c --- the input side was also
> > broken for BC/negative centuries. The attached patch fixes the input
> > side as well, and shows the old/fixed output for BC centuries. The test
> > queries were:
> >
> > SELECT to_date('-6 4', 'CC YY');
> > SELECT to_date(' 6 4', 'CC YY');
> > SELECT to_date('-6 00', 'CC YY');
> > SELECT to_date(' 6 00', 'CC YY');
> > SELECT to_date('-6', 'CC');
> > SELECT to_date(' 6', 'CC');
> >
> > I believe this is all for 9.3-only.
>
> OK, I found another bug in our code; the output of:
>
> SELECT to_date(' 6 BC', 'CC BC');
>
> returned 501BC, instead of the start of the century, 600BC, like SELECT
> to_date('-6', 'CC') does.
>
> I also allowed negative BC dates to map to AD dates, just like negative
> AD dates map to BC dates.
>
> Attached is an updated patch and output diff.
Applied.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6126: CC parameter in to_char() behaves incorrectly
On Sat, Aug 4, 2012 at 10:34:14AM -0400, Bruce Momjian wrote:
> > I am thinking it is too late to apply this for 9.2 because users might
> > have already tested their applications, though I doubt many are using BC
> > dates. Feedback?
>
> There is never just one bug in formatting.c --- the input side was also
> broken for BC/negative centuries. The attached patch fixes the input
> side as well, and shows the old/fixed output for BC centuries. The test
> queries were:
>
> SELECT to_date('-6 4', 'CC YY');
> SELECT to_date(' 6 4', 'CC YY');
> SELECT to_date('-6 00', 'CC YY');
> SELECT to_date(' 6 00', 'CC YY');
> SELECT to_date('-6', 'CC');
> SELECT to_date(' 6', 'CC');
>
> I believe this is all for 9.3-only.
OK, I found another bug in our code; the output of:
SELECT to_date(' 6 BC', 'CC BC');
returned 501BC, instead of the start of the century, 600BC, like SELECT
to_date('-6', 'CC') does.
I also allowed negative BC dates to map to AD dates, just like negative
AD dates map to BC dates.
Attached is an updated patch and output diff.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
new file mode 100644
index 4347ad3..6bd3fc6
*** a/src/backend/utils/adt/formatting.c
--- b/src/backend/utils/adt/formatting.c
*** DCH_to_char(FormatNode *node, bool is_in
*** 2640,2647
case DCH_CC:
if (is_interval) /* straight calculation */
i = tm->tm_year / 100;
! else /* century 21 starts in 2001 */
! i = (tm->tm_year - 1) / 100 + 1;
if (i <= 99 && i >= -99)
sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2, i);
else
--- 2640,2654
case DCH_CC:
if (is_interval) /* straight calculation */
i = tm->tm_year / 100;
! else
! {
! if (tm->tm_year > 0)
! /* Century 20 == 1901 - 2000 */
! i = (tm->tm_year - 1) / 100 + 1;
! else
! /* Century 6BC == 600BC - 501BC */
! i = tm->tm_year / 100 - 1;
! }
if (i <= 99 && i >= -99)
sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2, i);
else
*** do_to_timestamp(text *date_txt, text *fm
*** 3471,3497
*/
if (tmfc.cc && tmfc.yysz <= 2)
{
tm->tm_year = tmfc.year % 100;
if (tm->tm_year)
! tm->tm_year += (tmfc.cc - 1) * 100;
else
! tm->tm_year = tmfc.cc * 100;
}
else
tm->tm_year = tmfc.year;
}
else if (tmfc.cc) /* use first year of century */
- tm->tm_year = (tmfc.cc - 1) * 100 + 1;
-
- if (tmfc.bc)
{
! if (tm->tm_year > 0)
! tm->tm_year = -(tm->tm_year - 1);
else
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
! errmsg("inconsistent use of year %04d and \"BC\"",
! tm->tm_year)));
}
if (tmfc.j)
--- 3478,3511
*/
if (tmfc.cc && tmfc.yysz <= 2)
{
+ if (tmfc.bc)
+ tmfc.cc = -tmfc.cc;
tm->tm_year = tmfc.year % 100;
if (tm->tm_year)
! {
! if (tmfc.cc >= 0)
! tm->tm_year += (tmfc.cc - 1) * 100;
! else
! tm->tm_year = (tmfc.cc + 1) * 100 - tm->tm_year + 1;
! }
else
! tm->tm_year = tmfc.cc * 100 + ((tmfc.cc >= 0) ? 0 : 1);
}
else
+ {
tm->tm_year = tmfc.year;
+ if (tmfc.bc && tm->tm_year > 0)
+ tm->tm_year = -(tm->tm_year - 1);
+ }
}
else if (tmfc.cc) /* use first year of century */
{
! if (tmfc.bc)
! tmfc.cc = -tmfc.cc;
! if (tmfc.cc >= 0)
! tm->tm_year = (tmfc.cc - 1) * 100 + 1;
else
! tm->tm_year = tmfc.cc * 100 + 1;
}
if (tmfc.j)
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
new file mode 100644
index ab8faab..db2cfe6
*** a/src/test/regress/expected/timestamp.out
--- b/src/test/regress/expected/timestamp.out
*** SELECT '' AS to_char_3, to_char(d1, 'Y,Y
*** 992,998
| 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
| 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
| 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
!| 0,097 0097 097 97 7 01 1 02 07 047 16 3 1686042
| 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
| 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
| 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
--- 992,998
| 1,997 1997 997 97 7 20 1 02 07 045 1
Re: [BUGS] BUG #6126: CC parameter in to_char() behaves incorrectly
On Fri, Aug 3, 2012 at 06:51:45PM -0400, Bruce Momjian wrote:
> I also tested boundry values, e.g. 6th Century BC is 600-501:
>
> test=> select to_char('0600-01-01 00:00:00 BC' :: timestamp, 'CC');
>to_char
> -
>-6
> (1 row)
>
> test=> select to_char('0599-01-01 00:00:00 BC' :: timestamp, 'CC');
>to_char
> -
>-6
> (1 row)
>
> test=> select to_char('0501-01-01 00:00:00 BC' :: timestamp, 'CC');
>to_char
> -
>-6
> (1 row)
>
> test=> select to_char('0500-01-01 00:00:00 BC' :: timestamp, 'CC');
>to_char
> -
>-5
> (1 row)
>
> I am thinking it is too late to apply this for 9.2 because users might
> have already tested their applications, though I doubt many are using BC
> dates. Feedback?
There is never just one bug in formatting.c --- the input side was also
broken for BC/negative centuries. The attached patch fixes the input
side as well, and shows the old/fixed output for BC centuries. The test
queries were:
SELECT to_date('-6 4', 'CC YY');
SELECT to_date(' 6 4', 'CC YY');
SELECT to_date('-6 00', 'CC YY');
SELECT to_date(' 6 00', 'CC YY');
SELECT to_date('-6', 'CC');
SELECT to_date(' 6', 'CC');
I believe this is all for 9.3-only.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
new file mode 100644
index 4347ad3..7eb573d
*** a/src/backend/utils/adt/formatting.c
--- b/src/backend/utils/adt/formatting.c
*** DCH_to_char(FormatNode *node, bool is_in
*** 2640,2647
case DCH_CC:
if (is_interval) /* straight calculation */
i = tm->tm_year / 100;
! else /* century 21 starts in 2001 */
! i = (tm->tm_year - 1) / 100 + 1;
if (i <= 99 && i >= -99)
sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2, i);
else
--- 2640,2654
case DCH_CC:
if (is_interval) /* straight calculation */
i = tm->tm_year / 100;
! else
! {
! if (tm->tm_year > 0)
! /* Century 20 == 1901 - 2000 */
! i = (tm->tm_year - 1) / 100 + 1;
! else
! /* Century 6BC == 600BC - 501BC */
! i = tm->tm_year / 100 - 1;
! }
if (i <= 99 && i >= -99)
sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2, i);
else
*** do_to_timestamp(text *date_txt, text *fm
*** 3473,3487
{
tm->tm_year = tmfc.year % 100;
if (tm->tm_year)
! tm->tm_year += (tmfc.cc - 1) * 100;
else
! tm->tm_year = tmfc.cc * 100;
}
else
tm->tm_year = tmfc.year;
}
else if (tmfc.cc) /* use first year of century */
! tm->tm_year = (tmfc.cc - 1) * 100 + 1;
if (tmfc.bc)
{
--- 3480,3504
{
tm->tm_year = tmfc.year % 100;
if (tm->tm_year)
! {
! if (tmfc.cc >= 0)
! tm->tm_year += (tmfc.cc - 1) * 100;
! else
! tm->tm_year = (tmfc.cc + 1) * 100 - tm->tm_year + 1;
! }
else
! tm->tm_year = tmfc.cc * 100 + ((tmfc.cc >= 0) ? 0 : 1);
}
else
tm->tm_year = tmfc.year;
}
else if (tmfc.cc) /* use first year of century */
! {
! if (tmfc.cc >= 0)
! tm->tm_year = (tmfc.cc - 1) * 100 + 1;
! else
! tm->tm_year = tmfc.cc * 100 + 1;
! }
if (tmfc.bc)
{
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
new file mode 100644
index ab8faab..db2cfe6
*** a/src/test/regress/expected/timestamp.out
--- b/src/test/regress/expected/timestamp.out
*** SELECT '' AS to_char_3, to_char(d1, 'Y,Y
*** 992,998
| 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
| 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
| 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
!| 0,097 0097 097 97 7 01 1 02 07 047 16 3 1686042
| 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
| 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
| 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
--- 992,998
| 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
| 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
| 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
!| 0,097 0097 097 97 7 -1 1 02 07 047 16 3 1686
