Re: [GENERAL] not aborting transactions on failed select

[Alban Hertroys]

On 11 September 2013 07:53, Sergey Shelukhin  wrote:

> The query suffers from the auto-lower-casing of unquoted table names,
> which is not ANSI compliant. Technically we could add quotes (and stay
> ANSI), but then MySQL would break without explicitly setting it to use ANSI
> mode, so it's a lose-lose situation if we do not want to have DB-specific
> code.
>

Whether identifiers get folded to lower case or to upper case (which IS
according to the standard) should not matter to you.
If you're using case sensitive table names, the standard says to quote your
identifiers.

It would appear that MySQL is doing something non-standard compliant that's
biting you here.

I haven't used MySQL in 10 years, but if memory serves me correctly, it
tends to use the literal file name of the table, which would be
case-sensitive on a case-sensitive file system. I remember the hilarity of
the same issue when we moved a MySQL database from a file system that was
not case sensitive (NTFS) to one that was (EXT2FS)  - we had to rewrite all
our queries because MySQL could no longer find the tables in our queries.
That was when we wrote off MySQL for the project we were working on back
then.

>From your remark it isn't entirely clear what your problem is; it would
appear that your ORM is generating case-sensitive table names and that your
query outside the ORM assumes that it doesn't need to quote those
identifiers?
Perhaps your ORM can be told not to do that?

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

[GENERAL] problem in installation of postgresql-jdbc

[Vivek Singh Raghuwanshi]

Hi All,

I am trying to install postgresql-jdbc but facing java error. sun java is
already installed but postgresql-jdbc installing openjdj. please let me
know how to resolve this and if you require any other details.

[root@172-24-1-54 ~]# java -version
java version "1.6.0_33"
Java(TM) SE Runtime Environment (build 1.6.0_33-b04)
Java HotSpot(TM) 64-Bit Server VM (build 20.8-b03, mixed mode)
[root@172-24-1-54 ~]# yum install postgresql-jdbc.noarch
Dependencies Resolved

===
 PackageArch
Version  RepositorySize
===
Installing:
 postgresql-jdbcnoarch
8.4.701-8.el6redhat63 402 k
Installing for dependencies:
 flac   x86_64
1.2.1-6.1.el6redhat63 243 k
 giflib x86_64
4.1.6-3.1.el6redhat63  37 k
 java-1.6.0-openjdk x86_64
1:1.6.0.0-1.45.1.11.1.el6redhat63  25 M
 jline  noarch
0.9.94-0.8.el6   redhat63  86 k
 jpackage-utils noarch
1.7.5-3.12.el6   redhat63  59 k
 libasyncns x86_64
0.8-1.1.el6  redhat63  24 k
 libsndfile x86_64
1.0.20-5.el6 redhat63 233 k
 pulseaudio-libsx86_64
0.9.21-13.el6redhat63 462 k
 rhino  noarch
1.7-0.7.r2.2.el6 redhat63 778 k
 tzdata-javanoarch
2012c-1.el6  redhat63 154 k

Transaction Summary
===
Install  11 Package(s)






-- 
ViVek Raghuwanshi
Mobile -+91-09595950504
Skype - vivek_raghuwanshi
IRC vivekraghuwanshi
http://vivekraghuwanshi.wordpress.com/
http://in.linkedin.com/in/vivekraghuwanshi

Re: [GENERAL] problem in installation of postgresql-jdbc

[Albe Laurenz]

Vivek Singh Raghuwanshi wrote:
> I am trying to install postgresql-jdbc but facing java error.

It would be helpful to know which error you are facing.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [pgeu-general] [GENERAL] Call for design: PostgreSQL mugs

[Albe Laurenz]

patrick keshishian wrote:
> One more "cute" idea that came to me last night. Here is a very
> poor attempt at it by yours truly; keep in mind I'm not a graphics
> artist. This image is for illustration purposes only!
> 
>   http://sidster.com/gallery/2013/09/10/elephant_paw.sml.jpg
> 
> Can you picture a bunch of these on a meeting table? If that image
> does not scream "Stampede!", I don't know what does. =)

+1

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_largeobjects

[Raghavendra]

On Wed, Sep 11, 2013 at 10:19 AM, James Sewell wrote:

> Hello all,
>
> I have a table which makes use of pg_largeobjects. I am inserting rows
> into the table as user1. If I connect to the database as user2 I can SELECT
> data, but can not open the large object for reading (user1 can do this). I
> don't want to set lo_compat_privileges as then user3 (who can't SELECT
> from the services tables) would be able to read the large object.
>
>
GRANT SELECT,UPATE ON LARGE OBJECT to user2;

Will this work...

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

Re: [GENERAL] problem in installation of postgresql-jdbc

[Vivek Singh Raghuwanshi]

java version "1.6.0_33" is already installed but after firing yum install
postgresql-jdbc
java-1.6.0-openjdk is also going to installed as dependencies.


On Wed, Sep 11, 2013 at 1:53 PM, Albe Laurenz wrote:

> Vivek Singh Raghuwanshi wrote:
> > I am trying to install postgresql-jdbc but facing java error.
>
> It would be helpful to know which error you are facing.
>
> Yours,
> Laurenz Albe
>



-- 
ViVek Raghuwanshi
Mobile -+91-09595950504
Skype - vivek_raghuwanshi
IRC vivekraghuwanshi
http://vivekraghuwanshi.wordpress.com/
http://in.linkedin.com/in/vivekraghuwanshi

Re: [GENERAL] problem in installation of postgresql-jdbc

[John R Pierce]

On 9/11/2013 1:47 AM, Vivek Singh Raghuwanshi wrote:
java version "1.6.0_33" is already installed but after firing yum 
install postgresql-jdbc
java-1.6.0-openjdk is also going to installed as dependencies. 


We never install the RPM of the jdbc driver as its just a single .jar 
file... we just bundle the proper .jar file with our applications.




--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_largeobjects

[Kevin Grittner]

James Sewell  wrote:

> is there any reason to use pg_largeobjects if I am storing data
> under 1GB which doesn't require random reads any more?

If individual large objects might need to be referenced from
multiple locations, it gives you an easy way to do that without
needing to create a new table with id and document columns (and
possibly a comment column).

It gives you the ability to stream documents in rather than
including them in a SQL statement, which can reduce stress on RAM.

It gives you the ability to set the security for individual
documents, although it sounds like that is not something you find
useful.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Call for design: PostgreSQL mugs

[Eduardo Morras]

On Wed, 04 Sep 2013 00:08:52 +0200
Andreas 'ads' Scherbaum  wrote:

> 
> PostgreSQL folks!
> 
> We are looking for the next big thing. Actually, it's a bit smaller: a 
> new design for mugs. So far we had big blue elephants, small blue 
> elephants, frosty elephants, white SQL code on black mugs ... Now it's 
> time to design something new.
> 
> 
> What's in for you? Fame, of course - we will announce the designer of 
> the next mug along with the mugs itself. Plus 4 mugs for you and your 
> friends.
> 
> 
> Do you have a cool idea? Please let us know. Either reply here or send 
> an email to pgeu-bo...@postgresql.org.
 
CREATE OR REFILL mug{
  id_owner INTEGER PRIMARY KEY,
  owner_name VARCHAR(40),
  CONSTRAINT id_owner PRIMARY KEY (mug),
  CONSTRAINT fk_content FOREIGN KEY (content)
REFERENCES content (content_id) 
)WITH (
  OIDS=FALSE
);
 
> Thanks,


---   ---
Eduardo Morras 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] not aborting transactions on failed select

[Sergey Shelukhin]

Folding to uppercase is according to standard, lowercase no, so in our case
we could expect it to work if we have uppercase tables... in fact, it
should work whether FS is case sensitive or not in MySQL, tables are
created and used uppercase.
Due to presence of a large number of historical installations renaming all
the tables is not viable.
Again, as I said, this is not the problem here, we'll figure out how to
deal with the queries in this case. Probably put in quotes and switch MySQL
to ANSI mode within session, some testing would be needed to see how it
affects ORM.

But the problem is that even when queries work, for the future potential
breakage the fall-back to ORM should always work... and it doesn't.


On Wed, Sep 11, 2013 at 12:57 AM, Alban Hertroys  wrote:

> On 11 September 2013 07:53, Sergey Shelukhin wrote:
>
>> The query suffers from the auto-lower-casing of unquoted table names,
>> which is not ANSI compliant. Technically we could add quotes (and stay
>> ANSI), but then MySQL would break without explicitly setting it to use ANSI
>> mode, so it's a lose-lose situation if we do not want to have DB-specific
>> code.
>>
>
> Whether identifiers get folded to lower case or to upper case (which IS
> according to the standard) should not matter to you.
> If you're using case sensitive table names, the standard says to quote
> your identifiers.
>
> It would appear that MySQL is doing something non-standard compliant
> that's biting you here.
>
> I haven't used MySQL in 10 years, but if memory serves me correctly, it
> tends to use the literal file name of the table, which would be
> case-sensitive on a case-sensitive file system. I remember the hilarity of
> the same issue when we moved a MySQL database from a file system that was
> not case sensitive (NTFS) to one that was (EXT2FS)  - we had to rewrite all
> our queries because MySQL could no longer find the tables in our queries.
> That was when we wrote off MySQL for the project we were working on back
> then.
>
> From your remark it isn't entirely clear what your problem is; it would
> appear that your ORM is generating case-sensitive table names and that your
> query outside the ORM assumes that it doesn't need to quote those
> identifiers?
> Perhaps your ORM can be told not to do that?
>
> --
> If you can't see the forest for the trees,
> Cut the trees and you'll see there is no forest.
>

-- 
CONFIDENTIALITY NOTICE
NOTICE: This message is intended for the use of the individual or entity to 
which it is addressed and may contain information that is confidential, 
privileged and exempt from disclosure under applicable law. If the reader 
of this message is not the intended recipient, you are hereby notified that 
any printing, copying, dissemination, distribution, disclosure or 
forwarding of this communication is strictly prohibited. If you have 
received this communication in error, please contact the sender immediately 
and delete it from your system. Thank You.

Re: [GENERAL] not aborting transactions on failed select

[David Johnston]

Sergey Shelukhin wrote
> Due to presence of a large number of historical installations {doing such
> and such} is not viable.

Yeah, PostgreSQL faces this same issue

If you intend to stay here long, and we hope you do (welcome by the way), it
is customary to bottom-post on these lists.

One other thought is that exception generation and handling is expensive. 
It probably is a fair trade-off - since the ORM is already slow the added
overhead of the failing optimization query shouldn't be that noticeable. 
Failure means either bad code or incorrect pre-conditions; both of which
should be explicitly checked and reported on.  If those checks fail the
system can auto-configure to use the backup (ORM) channel instead of the
primary (optimized) channel.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/not-aborting-transactions-on-failed-select-tp5770387p5770478.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] invalid frontend message type 136

[Steve Crawford]

On 09/10/2013 11:30 AM, David Johnston wrote:

Steve Crawford wrote

Sorry, I should have included that. The error occurred when an older
client running 8.3.7 (I know, ferreting and finishing upgrades on
clients with old libraries is in progress) on CentOS 5.3 (32-bit). Of
all the machines connecting to the server, this one pretty lightly
loaded and typically creates a new connection to the server somewhat
over 10,000 times/week. This is the first instance of the error.

These machines/connections are buried behind firewalls in an automated
system and this particular connection is password-less.

The server is PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by
gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit.
Which client (not just the version):

psql; jdbc; .net; libpq


Unfortunately I'm not sure. There are many interrelated process that 
communicate with the server and since the connection bailed on connect 
there isn't much info to tell me which one. So it could be psql, code in 
one of several C-programs or connections from Perl or other scripting 
program. But they are all using the same libraries.





I am not surprised that an old client talking to a new server would cause a
protocol error to be issued.


Fair enough. But this is *one* error from among millions of successful 
connections in a farm of similar servers. This server happens to be one 
from an older series that is in-line for updates but is not the only one 
running this version. The nature of our operation along with some very 
specific requirement forced by specialized underlying hardware requires 
careful hand-rolling of updates to the machines.



   


I'm doubtful you will get much help on the mailing lists for something like
this

...

Actually, all I want to know is:

 1) A definition of the error - I can research further from there. 
PostgreSQL has by far the most outstanding documentation of any 
open-source (or possibly closed-source) product I use but it still lacks 
in a comprehensive (i.e. any) glossary of log/error messages.


2) An idea of whether or not to worry. If it is an odd and harmless 
transient (dealt with automatically by our 
error-checking/validation/recovery code) then I'll ignore it. But if 
it's something that could be an early indication of lurking corruption 
or pending failure then I need to investigate aggressively.


Cheers,
Steve



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] autovacuum out of memory errors

[tim truman]

Hi,

Starting this morning I have been getting out of memory errors from the
postgres autovacuum process. I have searched through previous similar
questions but not found anything other than suggestions to reduce either
'shared_buffers' or 'maintenance_work_mem' but these seem very instance
specific and would like a better understanding both of the root cause and
solution before making changes to my postgres setup.

We have a single postgres cluster installed from the Debian repository
hosting 36 databases the details of which are below. The table being
vacuumed at the time is about 19GB in size.

Errors from log:
TopMemoryContext: 232408 total in 13 blocks; 11792 free (8 chunks); 220616
used
  TopTransactionContext: 24576 total in 2 blocks; 23312 free (25 chunks);
1264 used
  Type information cache: 24576 total in 2 blocks; 11888 free (5 chunks);
12688 used
  Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512
used
  TOAST to main relid map: 24576 total in 2 blocks; 11872 free (5 chunks);
12704 used
  AV worker: 24576 total in 2 blocks; 19816 free (7 chunks); 4760 used
Autovacuum Portal: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
  Vacuum: 8192 total in 1 blocks; 8080 free (0 chunks); 112 used
  smgr relation table: 24576 total in 2 blocks; 13920 free (4 chunks);
10656 used
  TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks);
32 used
  Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
  PortalMemory: 0 total in 0 blocks; 0 free (0 chunks); 0 used
  Relcache by OID: 24576 total in 2 blocks; 13872 free (3 chunks); 10704
used
  CacheMemoryContext: 817840 total in 20 blocks; 171584 free (1 chunks);
646256 used
pbl_product_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
pbl_maxallocation_idx: 2048 total in 1 blocks; 440 free (0 chunks);
1608 used
pbl_endtimenotz_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296
used
pbl_endtimegmt_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296
used
pbl_begintimenotz_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296
used
pbl_begintimegmt_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296
used
pbl_allocated_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
productbookinglevel_product_id_key: 2048 total in 1 blocks; 656 free (0
chunks); 1392 used
productbookinglevel_pkey: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
pg_index_indrelid_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
pg_opclass_am_name_nsp_index: 3072 total in 2 blocks; 1448 free (4
chunks); 1624 used
pg_foreign_data_wrapper_name_index: 3072 total in 2 blocks; 1744 free
(3 chunks); 1328 used
pg_enum_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328
used
pg_class_relname_nsp_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_foreign_server_oid_index: 3072 total in 2 blocks; 1744 free (3
chunks); 1328 used
pg_statistic_relid_att_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_cast_source_target_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
pg_language_name_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
pg_authid_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376
used
pg_amop_fam_strat_index: 3072 total in 2 blocks; 1384 free (2 chunks);
1688 used
pg_index_indexrelid_index: 3072 total in 2 blocks; 1696 free (2
chunks); 1376 used
pg_ts_template_tmplname_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_ts_config_map_index: 3072 total in 2 blocks; 1496 free (4 chunks);
1576 used
pg_opclass_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
1376 used
pg_foreign_data_wrapper_oid_index: 3072 total in 2 blocks; 1744 free (3
chunks); 1328 used
pg_auth_members_member_role_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_ts_dict_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
pg_conversion_default_index: 3072 total in 2 blocks; 1432 free (3
chunks); 1640 used
pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 1432 free (3
chunks); 1640 used
pg_trigger_tgrelid_tgname_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
pg_enum_typid_label_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_ts_config_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
pg_user_mapping_oid_index: 3072 total in 2 blocks; 1744 free (3
chunks); 1328 used
pg_opfamily_am_name_nsp_index: 3072 total in 2 blocks; 1496 free (4
chunks); 1576 used
pg_type_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376
used
pg_aggregate_fnoid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
pg_constraint_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
pg_rewrite_rel_rulename_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_ts_parse

[GENERAL] PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

[Andreas]

Hi,

*I ran into a major problem when I tried to import a backup from 9.1. 
into a 9.3 PG.*


I just installed PG 9.3 on a new linux box.
Then I wanted to import a plaintext dump of a DB that was created by 
pg_dump of PG 9.1


There are a lot of views that have joins to a subquery in the from-clause.

something like

SELECT ... some columns ...
FROM
maintable AS m
JOIN someflag AS f ON m.flag_1_id = f.id
LEFT JOIN
(
child_table AS   c
JOIN   someotherflag  AS  f   ON   c.flag_2_id = f.id
)   AS x  ON m.id = x.main_id

This works with PG 9.1 and PG 9.2 but PG 9.3 complains:

ERROR:  table name "f" specified more than once

*Are there no separate namespaces for subqueries anymore in PG 9.3 ?*

Do I have to change ALL those views in the old PG 9.1 server before I 
can import the backup into 9.3 or is there another way to work around 
this issue ?

Re: [GENERAL] PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

[Adrian Klaver]

On 09/11/2013 09:53 AM, Andreas wrote:

Hi,

*I ran into a major problem when I tried to import a backup from 9.1.
into a 9.3 PG.*

I just installed PG 9.3 on a new linux box.
Then I wanted to import a plaintext dump of a DB that was created by
pg_dump of PG 9.1

There are a lot of views that have joins to a subquery in the from-clause.

something like

SELECT ... some columns ...
FROM
 maintable AS m
 JOIN someflag AS f ON m.flag_1_id = f.id
LEFT JOIN
(
 child_table AS   c
 JOIN   someotherflag  AS  f   ON   c.flag_2_id = f.id
)   AS x  ON m.id = x.main_id

This works with PG 9.1 and PG 9.2 but PG 9.3 complains:

ERROR:  table name "f" specified more than once

*Are there no separate namespaces for subqueries anymore in PG 9.3 ?*

Do I have to change ALL those views in the old PG 9.1 server before I
can import the backup into 9.3 or is there another way to work around
this issue ?


My guess you are seeing this:

http://www.postgresql.org/docs/9.3/interactive/release-9-3.html

Improve view/rule printing code to handle cases where referenced tables 
are renamed, or columns are renamed, added, or dropped (Tom Lane)


Table and column renamings can produce cases where, if we merely 
substitute the new name into the original text of a rule or view, the 
result is ambiguous. This change fixes the rule-dumping code to insert 
manufactured table and column aliases when needed to preserve the 
original semantics.



You would be advised to use the 9.3 version of pg_dump to dump the 9.1 
database.



--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

[David Johnston]

Andreas-3-2 wrote
> Hi,
> 
> *I ran into a major problem when I tried to import a backup from 9.1. 
> into a 9.3 PG.*
> 
> I just installed PG 9.3 on a new linux box.
> Then I wanted to import a plaintext dump of a DB that was created by 
> pg_dump of PG 9.1
> 
> There are a lot of views that have joins to a subquery in the from-clause.
> 
> something like
> 
> SELECT ... some columns ...
> FROM
>  maintable AS m
>  JOIN someflag AS f ON m.flag_1_id = f.id
> LEFT JOIN
> (
>  child_table AS   c
>  JOIN   someotherflag  AS  f   ON   c.flag_2_id = f.id
> )   AS x  ON m.id = x.main_id
> 
> This works with PG 9.1 and PG 9.2 but PG 9.3 complains:
> 
> ERROR:  table name "f" specified more than once
> 
> *Are there no separate namespaces for subqueries anymore in PG 9.3 ?*
> 
> Do I have to change ALL those views in the old PG 9.1 server before I 
> can import the backup into 9.3 or is there another way to work around 
> this issue ?

Not running 9.3 yet so cannot test this myself.  Ignore the pg_dump and
view.  Does (should) the above query work if executed in psql?

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PG-9-3-complains-about-specified-more-than-once-Those-views-worked-in-PG-9-1-9-2-tp5770489p5770510.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] autovacuum out of memory errors

[Kevin Grittner]

tim truman  wrote:

> [ memory usage map ]

There doesn't seem to be any memory context using an unusually large amount of 
RAM.

>  522f9128.1151 ERROR:  out of memory
>  522f9128.1151 DETAIL:  Failed on request of size 336150396.
>  522f9128.1151 CONTEXT:  automatic vacuum of table "client.public.product"
>
> SELECT version();
> PostgreSQL 8.4.11 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real 
> (Debian 4.4.5-8) 4.4.5, 64-bit

http://www.postgresql.org/support/versioning/


> maintenance_work_mem | 1GB
> max_connections  | 1000

> shared_buffers   | 11GB

> wal_buffers  | 8MB

> work_mem | 8MB

> free -m
>  total   used   free shared    buffers cached
> Mem: 48396  48139    257  0    173  44771
> -/+ buffers/cache:   3194  45201
> Swap:  956 20    936

Clearly the free command was not run while the database service was
running.  Can we see what that looks like when it is running and
handling roughly the same workload that has been causing problems
(including any non-database processes which have been running on
the machine during failures)?

-- 
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

[David Johnston]

Adrian Klaver-3 wrote
> My guess you are seeing this:
> 
> http://www.postgresql.org/docs/9.3/interactive/release-9-3.html
> 
> Improve view/rule printing code to handle cases where referenced tables 
> are renamed, or columns are renamed, added, or dropped (Tom Lane)
> 
> Table and column renamings can produce cases where, if we merely 
> substitute the new name into the original text of a rule or view, the 
> result is ambiguous. This change fixes the rule-dumping code to insert 
> manufactured table and column aliases when needed to preserve the 
> original semantics.
> 
> 
> You would be advised to use the 9.3 version of pg_dump to dump the 9.1 
> database.

Maybe; but the supplied query does not seem to be ambiguous and the dump
phase has already completed.  pg_restore simply issues a CREATE VIEW and
does not perform interpolation of the contents.  If the select statement is
indeed correctly represented then I could very well see creating one like
that by hand and inserting it as part of an external database schema
installation (i.e., not via pg_restore) and would expect it to work. 
According to this such a scenario should also fail with the same message.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PG-9-3-complains-about-specified-more-than-once-Those-views-worked-in-PG-9-1-9-2-tp5770489p5770512.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

[Andreas]

Am 11.09.2013 21:02, schrieb David Johnston:

Andreas-3-2 wrote

Hi,

*I ran into a major problem when I tried to import a backup from 9.1.
into a 9.3 PG.*

I just installed PG 9.3 on a new linux box.
Then I wanted to import a plaintext dump of a DB that was created by
pg_dump of PG 9.1

There are a lot of views that have joins to a subquery in the from-clause.

something like

SELECT ... some columns ...
FROM
  maintable AS m
  JOIN someflag AS f ON m.flag_1_id = f.id
LEFT JOIN
(
  child_table AS   c
  JOIN   someotherflag  AS  f   ON   c.flag_2_id = f.id
)   AS x  ON m.id = x.main_id

This works with PG 9.1 and PG 9.2 but PG 9.3 complains:

ERROR:  table name "f" specified more than once

*Are there no separate namespaces for subqueries anymore in PG 9.3 ?*

Do I have to change ALL those views in the old PG 9.1 server before I
can import the backup into 9.3 or is there another way to work around
this issue ?

Not running 9.3 yet so cannot test this myself.  Ignore the pg_dump and
view.  Does (should) the above query work if executed in psql?

David J.



No, it doesn't work in psql of PG 9.3. I got from EnterpriseDB

I did check like this:
Open pgAdmin 1.18 of PG 9.3
Open connections to both servers ... to the remote 9.1 and the 9.3 here 
in my LAN.
Take the SQL definition of one of the problematic views out of the 9.1 
server as pgAdmin displays it.

Paste this into a sql editor window of the 9.3 server and execute it.
The creation of the view is rejected.
When I try to run the query within the view directly PG 9.3 balks too.

It doesn't accept the reused alias within the subquery.
This internal alias references not even the same table as the one 
outside the subquery.


It appeares as if there is just a global namespace for the whole query 
that spans over the names within the subquery, too.


If this is the case then I can't switch to PG 9.3 at all, because I had 
to reevaluate every query at my application throws at the DB and not 
only those 70 views that get rejected while the initial import of the 
sql dump.






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

[David Johnston]

Andreas-3-2 wrote
> No, it doesn't work in psql of PG 9.3. I got from EnterpriseDB

Can you please create a minimal self-contained query that exhibits this
behavior and file a bug report?

I quickly cobbled this together - works on 9.0.x

WITH
  tblA (a_id, a_val) AS ( VALUES (1::integer, 'one-a'::varchar) )
, tblB (b_id, b_val) AS ( VALUES (1::integer, 'one-b'::varchar) )
, tblC (c_id, c_val) AS ( VALUES (1::integer, 'one-c'::varchar) )
SELECT *
FROM (
tblA AS dup_tbl JOIN tblB ON (dup_tbl.a_id = tblB.b_id)
) AS refD
JOIN tblC AS dup_tbl ON (dup_tbl.c_id = refD.a_id)

If it fails on 9.3 it would do.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PG-9-3-complains-about-specified-more-than-once-Those-views-worked-in-PG-9-1-9-2-tp5770489p5770519.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

[David Johnston]

Try these too, please:

WITH 
  tblA (a_id, a_val) AS ( VALUES (1::integer, 'one-1'::varchar) )
, tblB (b_id, b_val) AS ( VALUES (1::integer, 'one-b'::varchar) )
, tblC (c_id, c_val) AS ( VALUES (1::integer, 'one-c'::varchar) )
SELECT *
FROM (
tblA JOIN tblB ON (tblA.a_id = tblB.b_id)
) AS refD
JOIN tblA ON (tblA.a_id = refD.a_id)
;

WITH 
  tblA (a_id, a_val) AS ( VALUES (1::integer, 'one-1'::varchar) )
, tblB (b_id, b_val) AS ( VALUES (1::integer, 'one-b'::varchar) )
, tblC (c_id, c_val) AS ( VALUES (1::integer, 'one-c'::varchar) )
SELECT *
FROM (
tblA JOIN tblB ON (tblA.a_id = tblB.b_id)
) AS refD
JOIN tblA ON (tblA.a_id = tblB.a_id)
; --this one should fail trying to reference tblB


The reference to tblA is used inside refD and outside of it as well; but no
table aliases are used.

Thanks

David J.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PG-9-3-complains-about-specified-more-than-once-Those-views-worked-in-PG-9-1-9-2-tp5770489p5770520.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

[Andreas]

Hi David,
your 3 examples work as you expected. That is 1+2 work and 3 throws an 
error.


I tried to figure out an example and found something peculiar.
The issue arises when there is another join in the subquery after the 
one with the reused table alias.

There is no error without this following join.

Look at this rather chunky sample.
If I join flag_2 before flag_1 it works.
It won't with flag_2 after flag_1.

The query works as soon as the reused alias joins last in the subquery.
If there are 2 reused aliases then the query wont work at all without 
renaming one alias.


The error shows with pgAdmin aws well as psql (9.3).

So probaply it's not an namespace issue but the query-parser screws up.   :(

I never placed a bug-report.  :}
Could you give a hand?


droptable if exists sub_tab;
droptable if exists main_tab;
droptable if exists flag_1;
droptable if exists flag_2;

create  temporary table flag_1 ( flag_1_id integer primary key, flag_1_t 
text );

insert into flag_1 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );

create  temporary table flag_2 ( flag_2_id integer primary key, flag_2_t 
text );

insert into flag_2 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );

create  temporary table main_tab ( main_id integer primary key, main_t 
text, flag_1_id integer references flag_1 ( flag_1_id ) );
insert into main_tab values ( 1, 'Main 1', 1 ), ( 2, 'Main 2', 2 ), ( 3, 
'Main 3', 3 );


create  temporary table sub_tab ( sub_id integer primary key, sub_t 
text, main_id integer references main_tab ( main_id ), flag_1_id integer 
references flag_1 ( flag_1_id ), flag_2_id integer references flag_2 ( 
flag_2_id ) );
insert into sub_tab values ( 1, 'Sub 1', 1, 3, 1 ), ( 2, 'Sub 2', 2, 2, 
2 ), ( 3, 'Sub 3', 3, 1, 3 );



select  m.main_id, m.main_t, f.flag_1_t,
x.sub_id, x.sub_t, x.flag_1_t, x.flag_2_t
frommain_tabas  m
joinflag_1  as  f   using   ( flag_1_id )
left join
(
sub_tab as  s
joinflag_2  as  f2  using   ( flag_2_id )   -- that 
way it works

joinflag_1  as  f   using   ( flag_1_id )
--joinflag_2  as  f2  using   ( flag_2_id ) -- that 
way it doesn't work

)   as  x   using   ( main_id );



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

[Andreas]

Just another addition...

If I remove the aliases for the tbles flag_1 and flag_2 the problem 
still comes up.


So one either has to mind the order of the joins or use unique aliases.

It's really an issue as there are bound to be some queries in 
sql-functions or some that get assembled dynamically in my application.
Those won't get executed in the initial import of the db-dump but will 
come up anytime later when the query gets used the first time.


This is a no go:(


regards
Andreas


Am 12.09.2013 00:33, schrieb Andreas:

Hi David,
your 3 examples work as you expected. That is 1+2 work and 3 throws an 
error.


I tried to figure out an example and found something peculiar.
The issue arises when there is another join in the subquery after the 
one with the reused table alias.

There is no error without this following join.

Look at this rather chunky sample.
If I join flag_2 before flag_1 it works.
It won't with flag_2 after flag_1.

The query works as soon as the reused alias joins last in the subquery.
If there are 2 reused aliases then the query wont work at all without 
renaming one alias.


The error shows with pgAdmin aws well as psql (9.3).

So probaply it's not an namespace issue but the query-parser screws 
up.   :(


I never placed a bug-report.  :}
Could you give a hand?


droptable if exists sub_tab;
droptable if exists main_tab;
droptable if exists flag_1;
droptable if exists flag_2;

create  temporary table flag_1 ( flag_1_id integer primary key, 
flag_1_t text );

insert into flag_1 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );

create  temporary table flag_2 ( flag_2_id integer primary key, 
flag_2_t text );

insert into flag_2 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );

create  temporary table main_tab ( main_id integer primary key, main_t 
text, flag_1_id integer references flag_1 ( flag_1_id ) );
insert into main_tab values ( 1, 'Main 1', 1 ), ( 2, 'Main 2', 2 ), ( 
3, 'Main 3', 3 );


create  temporary table sub_tab ( sub_id integer primary key, sub_t 
text, main_id integer references main_tab ( main_id ), flag_1_id 
integer references flag_1 ( flag_1_id ), flag_2_id integer references 
flag_2 ( flag_2_id ) );
insert into sub_tab values ( 1, 'Sub 1', 1, 3, 1 ), ( 2, 'Sub 2', 2, 
2, 2 ), ( 3, 'Sub 3', 3, 1, 3 );



select  m.main_id, m.main_t, f.flag_1_t,
x.sub_id, x.sub_t, x.flag_1_t, x.flag_2_t
frommain_tabas  m
joinflag_1  as  f   using   ( flag_1_id )
left join
(
sub_tab as  s
joinflag_2  as  f2  using   ( flag_2_id )   -- 
that way it works

joinflag_1  as  f   using   ( flag_1_id )
--joinflag_2  as  f2  using   ( flag_2_id ) -- 
that way it doesn't work

)   as  x   using   ( main_id );







--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

[David Johnston]

Andreas-3-2 wrote
> I never placed a bug-report.  :}
> Could you give a hand?

Sure.

PostgreSQL homepage (postgresql.org)
"Report a Bug" link on right-hand side of page.
Fill in the form.

>>>SEND THIS


> droptable if exists sub_tab;
> droptable if exists main_tab;
> droptable if exists flag_1;
> droptable if exists flag_2;
> 
> create  temporary table flag_1 ( flag_1_id integer primary key, flag_1_t 
> text );
> insert into flag_1 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );
> 
> create  temporary table flag_2 ( flag_2_id integer primary key, flag_2_t 
> text );
> insert into flag_2 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );
> 
> create  temporary table main_tab ( main_id integer primary key, main_t 
> text, flag_1_id integer references flag_1 ( flag_1_id ) );
> insert into main_tab values ( 1, 'Main 1', 1 ), ( 2, 'Main 2', 2 ), ( 3, 
> 'Main 3', 3 );
> 
> create  temporary table sub_tab ( sub_id integer primary key, sub_t 
> text, main_id integer references main_tab ( main_id ), flag_1_id integer 
> references flag_1 ( flag_1_id ), flag_2_id integer references flag_2 ( 
> flag_2_id ) );
> insert into sub_tab values ( 1, 'Sub 1', 1, 3, 1 ), ( 2, 'Sub 2', 2, 2, 
> 2 ), ( 3, 'Sub 3', 3, 1, 3 );
> 
> 
> select  m.main_id, m.main_t, f.flag_1_t,
>  x.sub_id, x.sub_t, x.flag_1_t, x.flag_2_t
> frommain_tabas  m
>  joinflag_1  as  f   using   ( flag_1_id )
>  left join
>  (
>  sub_tab as  s
>  joinflag_2  as  f2  using   ( flag_2_id )   -- that 
> way it works
>  joinflag_1  as  f   using   ( flag_1_id )
> --joinflag_2  as  f2  using   ( flag_2_id ) -- that 
> way it doesn't work
>  )   as  x   using   ( main_id );





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PG-9-3-complains-about-specified-more-than-once-Those-views-worked-in-PG-9-1-9-2-tp5770489p5770534.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Risk of set system wise statement_timeout

[Alex Lai]

I have been reading few posted comment about the risk for autovacuum for 
older postgres liek version 8.
I am currently running 9.2.4.  We have a need to terminate any query 
running longer than 2 hours.  Most of our query should finish within 15 
minutes.  We don't have very large amount of changes in the system and 
we run autovacuum daily.  Running the larger table for autovacuum should 
be fast.  Under my situation, setting statement_timeout = 720 which 
is 2 hours seems very low risk trigger fail to autovacuum.  Any one have 
any idea not to do it or any workaround to decrease the risk of fail 
autovacuum.


--
Best regards,


Alex Lai
OMI SIPS DBA ADNET Systems , Inc.
m...@sesda3.com



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Odd behavior in functions w/ anyarray & anyelement

[Joshua Burns]

Greetings,

I'm trying to track down some undocumented (or perhaps not well documented)
behavior I'm encountering in regards to custom functions (in plpgsql)
utilizing anyelement and anyarray as arguments and/or return types.

I arrived at this point when I was attempting to write the function
"ANYARRAY_REMOVE(anyarray, anyelement)", which returned anyarray.
This function would succeed when calling: SELECT
ANYARRAY_REMOVE(ARRAY[1,2,3], 2)
... But would fail when calling: SELECT ANYARRAY_REMOVE(ARRAY[1,2,3],
ARRAY[1,2])
... With the error: function anyarray_remove(integer[], integer[]) does not
exist.

>From that point I wrote a bunch of simply anyarray/element related
functions to better understand how these pseudo-types behave, which has
left me more confused than when I started.

Here are those functions, queries to interface with those functions, and
what I would expect each query to return or throw vs. what actually happens.

Nothing from what I have read and understand at these URLs document this
behavior:
-
http://forums.devshed.com/postgresql-help-21/what-s-anyarray-isn-t-it-the-same-as-array-148195.html
- http://www.postgresql.org/message-id/44649bb2.50...@tada.se
- http://www.postgresql.org/docs/9.1/static/extend-type-system.html

Queries Tested On:
- Windows 2003 R2 (64-Bit), PostgreSQL 9.1.0
- Ubuntu Linux 12.04 LTS (64-bit), PostgreSQL 9.2.4

/**/


DROP FUNCTION IF EXISTS anyel_anyel(anyelement);
CREATE OR REPLACE FUNCTION anyel_anyel(anyelement) RETURNS anyelement AS
$BODY$
BEGIN
RETURN $1;
END;
$BODY$ LANGUAGE plpgsql;

/**/

-- Works as expected. Should accept TEXT, should output TEXT to match input
data-type.
--   Expected: Returns "hiho" as TEXT
--   Actual:   Returns "hiho" as TEXT
SELECT anyel_anyel('hiho'::TEXT);

-- Works as expected. Should accept INTEGER, should output INTEGER to match
input data-type.
---  Expected: Returns "1" as INTEGER
---  Actual:   Returns "1" as INTEGER
SELECT anyel_anyel(1::INTEGER);

-- Works as expected. Should accept TEXT[], should output TEXT[] to match
input data-type.
--   Expected: Returns "{one,two}" as TEXT[]
--   Actual:   Returns "{one,two}" as TEXT[]
SELECT anyel_anyel(ARRAY['one', 'two']::TEXT[]);

-- Works as expected. Should accept INTEGER[], should output INTEGER[] to
match input data-type.
--   Expected: Returns "{1,2,3}" as INTEGER[]
--   Actual:   Returns "{1,2,3}" as INTEGER[]
SELECT anyel_anyel(ARRAY[1,2,3]::INTEGER[]);


/**/


DROP FUNCTION IF EXISTS anyar_anyar(anyarray);
CREATE OR REPLACE FUNCTION anyar_anyar(anyarray) RETURNS anyarray AS $BODY$
BEGIN
RETURN $1;
END;
$BODY$ LANGUAGE plpgsql;

/**/

-- Works as expected. Should not accept TEXT because not an array.
--   Expected: ERROR: function anyar_anyar(text) does not exist
--   Actual:   ERROR: function anyar_anyar(text) does not exist
SELECT anyar_anyar('hiho'::TEXT);

-- Works as expected. Should not accept INTEGER because not an array.
--   Expected: Throws ERROR: function anyar_anyar(integer) does not exist
--   Actual:   Throws ERROR: function anyar_anyar(integer) does not exist
SELECT anyar_anyar(1::INTEGER);

-- Works as expected. Should accept TEXT[], should output TEXT[].
--   Expected: Returns "{one,two}" as TEXT[]
--   Actual:   Returns "{one,two}" as TEXT[]
SELECT anyar_anyar(ARRAY['one', 'two']::TEXT[]);

-- Works as expected. Should accept INTEGER[], should output INTEGER[].
--   Expected: Returns "{1,2,3}" as INTEGER[]
--   Actual:   Returns "{1,2,3}" as INTEGER[]
SELECT anyar_anyar(ARRAY[1,2,3]::INTEGER[]);


/**/


DROP FUNCTION IF EXISTS anyar_anyel(anyarray);
CREATE OR REPLACE FUNCTION anyar_anyel(anyarray) RETURNS anyelement AS
$BODY$
BEGIN
RETURN $1;
END;
$BODY$ LANGUAGE plpgsql;

/**/

-- Works as expected. Should not accept TEXT because not an array.
--   Expected: ERROR: function anyar_anyel(text) does not exist
--   Actual:   ERROR: function anyar_anyel(text) does not exist
SELECT anyar_anyel('hiho'::TEXT);

-- Works as expected: Should not accept INTEGER because not an array.
--   Expected: ERROR: function anyar_anyel(integer) does not exist
--   Actual:   function anyar_anyel(integer) does not exist
SELECT anyar_anyel(1::INTEGER);

-- Does not work as expected. Should accept TEXT[], should output TEXT[] to
match input data-type.
--   Expected: Returns "{one,two}" as TEXT[]
--   Actual:   Returns "{one,two}" as TEXT
SELECT anyar_anyel(ARRAY['one', 'two']::TEXT[]);

-- Does not work as expected. Should accept INTEGER[], should output
INTEGER[] to match input da

[GENERAL] ZFS snapshots - to use pg_start_backup() and pg_stop_backup() or no?

[Gregory Haase]

I was trying to figure out how to get the following syntax to work:

echo "select pg_start_backup('zfs_snapshot'); \\! zfs snapshot
zroot/zpgsql@test; \\ select pg_stop_backup();" | psql postgres

The above command successfully starts the backup and creates the snapshot
but then fails to stop the backup. I've tried various combinations of \ and
\\ here with different whitespace and I just can't seem to find a
combination that works. I don't understand the proper use of \\ (described
as the separator metacommand).

However, in my research, I noted that a bunch of people seem to just not
even bother with pg_start_backup/pg_stop_backup and I guess aren't that
worried about the crash recovery process if they need to perform a restore.
I also find the omission of the start/stop backup functions from the File
System Level Backup page:
http://www.postgresql.org/docs/9.2/static/backup-file.html

Is the pg_start_backup() and pg_stop_backup() even necessary?

It would be nice to understand the proper syntax for the psql pipe,
regardless whether or not it is even necessary in this case.

Thanks,

Greg Haase

Re: [GENERAL] ZFS snapshots - to use pg_start_backup() and pg_stop_backup() or no?

[Steven Schlansker]

On Sep 11, 2013, at 4:29 PM, Gregory Haase  wrote:

> I was trying to figure out how to get the following syntax to work:
> 
> echo "select pg_start_backup('zfs_snapshot'); \\! zfs snapshot 
> zroot/zpgsql@test; \\ select pg_stop_backup();" | psql postgres

I do:

psql -c "select pg_start_backup('whatever');" && zfs snapshot pool/fs@sn && 
psql -c "select pg_stop_backup();"

That way no need to shell out from psql :) 

> 
> The above command successfully starts the backup and creates the snapshot but 
> then fails to stop the backup. I've tried various combinations of \ and \\ 
> here with different whitespace and I just can't seem to find a combination 
> that works. I don't understand the proper use of \\ (described as the 
> separator metacommand).

Keep in mind that echo "\\"  will actually only echo '\' because \ is a shell 
escape as well...

> 
> However, in my research, I noted that a bunch of people seem to just not even 
> bother with pg_start_backup/pg_stop_backup and I guess aren't that worried 
> about the crash recovery process if they need to perform a restore. I also 
> find the omission of the start/stop backup functions from the File System 
> Level Backup page: http://www.postgresql.org/docs/9.2/static/backup-file.html
> 
> Is the pg_start_backup() and pg_stop_backup() even necessary?
> 

If all of your Postgres files are part of *the same* consistent snapshot (i.e. 
are on one FS that gets snapshotted), then the start/stop backup should not be 
necessary.  It will just look like a server crash instead.

pg_start_backup is used when you do not have filesystem snapshotting available, 
and is described in detail on the next manual page:

http://www.postgresql.org/docs/9.2/static/continuous-archiving.html



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] ZFS snapshots - to use pg_start_backup() and pg_stop_backup() or no?

[Gregory Haase]

Good point on not needing to shell out. I think my process was a mental
holdover from the fact that MySQL releases 'flush tables with read lock' on
client disconnect.

Typically how fast is a crash recovery for a ~1TB database with heavy OTLP
load? Are we talking several seconds, several minutes, several hours?

Thanks,

-G


On Wed, Sep 11, 2013 at 4:46 PM, Steven Schlansker wrote:

>
> On Sep 11, 2013, at 4:29 PM, Gregory Haase 
> wrote:
>
> > I was trying to figure out how to get the following syntax to work:
> >
> > echo "select pg_start_backup('zfs_snapshot'); \\! zfs snapshot
> zroot/zpgsql@test; \\ select pg_stop_backup();" | psql postgres
>
> I do:
>
> psql -c "select pg_start_backup('whatever');" && zfs snapshot pool/fs@sn&& 
> psql -c "select pg_stop_backup();"
>
> That way no need to shell out from psql :)
>
> >
> > The above command successfully starts the backup and creates the
> snapshot but then fails to stop the backup. I've tried various combinations
> of \ and \\ here with different whitespace and I just can't seem to find a
> combination that works. I don't understand the proper use of \\ (described
> as the separator metacommand).
>
> Keep in mind that echo "\\"  will actually only echo '\' because \ is a
> shell escape as well...
>
> >
> > However, in my research, I noted that a bunch of people seem to just not
> even bother with pg_start_backup/pg_stop_backup and I guess aren't that
> worried about the crash recovery process if they need to perform a restore.
> I also find the omission of the start/stop backup functions from the File
> System Level Backup page:
> http://www.postgresql.org/docs/9.2/static/backup-file.html
> >
> > Is the pg_start_backup() and pg_stop_backup() even necessary?
> >
>
> If all of your Postgres files are part of *the same* consistent snapshot
> (i.e. are on one FS that gets snapshotted), then the start/stop backup
> should not be necessary.  It will just look like a server crash instead.
>
> pg_start_backup is used when you do not have filesystem snapshotting
> available, and is described in detail on the next manual page:
>
> http://www.postgresql.org/docs/9.2/static/continuous-archiving.html
>
>

Re: [GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

[Andreas]

I took your sample and modified it a bit.

ERROR:  table name "tblb" specified more than once
Switch tblB and tblC in the subquery and it works.

WITH
  tblA (a_id, a_val) AS ( VALUES (1::integer, 'one-1'::varchar) )
, tblB (b_id, b_val) AS ( VALUES (1::integer, 'one-b'::varchar) )
, tblC (c_id, c_val) AS ( VALUES (1::integer, 'one-c'::varchar) )

select  *
fromtblA
jointblBon  tblA.a_id = tblB.b_id
join(
tblB
jointblCon  tblC.c_id = tblB.b_id
)   as  x   on  tblA.a_id = x.c_id;




Am 12.09.2013 00:52, schrieb David Johnston:

Andreas-3-2 wrote

I never placed a bug-report.  :}
Could you give a hand?

Sure.

PostgreSQL homepage (postgresql.org)
"Report a Bug" link on right-hand side of page.
Fill in the form.


SEND THIS



droptable if exists sub_tab;
droptable if exists main_tab;
droptable if exists flag_1;
droptable if exists flag_2;

create  temporary table flag_1 ( flag_1_id integer primary key, flag_1_t
text );
insert into flag_1 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );

create  temporary table flag_2 ( flag_2_id integer primary key, flag_2_t
text );
insert into flag_2 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );

create  temporary table main_tab ( main_id integer primary key, main_t
text, flag_1_id integer references flag_1 ( flag_1_id ) );
insert into main_tab values ( 1, 'Main 1', 1 ), ( 2, 'Main 2', 2 ), ( 3,
'Main 3', 3 );

create  temporary table sub_tab ( sub_id integer primary key, sub_t
text, main_id integer references main_tab ( main_id ), flag_1_id integer
references flag_1 ( flag_1_id ), flag_2_id integer references flag_2 (
flag_2_id ) );
insert into sub_tab values ( 1, 'Sub 1', 1, 3, 1 ), ( 2, 'Sub 2', 2, 2,
2 ), ( 3, 'Sub 3', 3, 1, 3 );


select  m.main_id, m.main_t, f.flag_1_t,
  x.sub_id, x.sub_t, x.flag_1_t, x.flag_2_t
frommain_tabas  m
  joinflag_1  as  f   using   ( flag_1_id )
  left join
  (
  sub_tab as  s
  joinflag_2  as  f2  using   ( flag_2_id )   -- that
way it works
  joinflag_1  as  f   using   ( flag_1_id )
--joinflag_2  as  f2  using   ( flag_2_id ) -- that
way it doesn't work
  )   as  x   using   ( main_id );





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PG-9-3-complains-about-specified-more-than-once-Those-views-worked-in-PG-9-1-9-2-tp5770489p5770534.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_largeobjects

[James Sewell]

Hey,

This does work, but as I'm using DEFAULT PRIVs to give access to tables it
becomes a (the only) step which can't be done at schema creation time and
has to be done at data insertion time.

It feels to me that ALTER DEFAULT PRIVILEGES should be extended to support
large objects (either by default from the table permissions or as a new
GRANT option). Thoughts on this?

Cheers,



James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


 Level 2, 50 Queen St, Melbourne VIC 3000

*P* (03) 8370 8000 * **W* www.lisasoft.com  *F*(03) 8370 8000



On Wed, Sep 11, 2013 at 6:40 PM, Raghavendra <
raghavendra@enterprisedb.com> wrote:

>
>
> On Wed, Sep 11, 2013 at 10:19 AM, James Sewell 
> wrote:
>
>> Hello all,
>>
>> I have a table which makes use of pg_largeobjects. I am inserting rows
>> into the table as user1. If I connect to the database as user2 I can SELECT
>> data, but can not open the large object for reading (user1 can do this). I
>> don't want to set lo_compat_privileges as then user3 (who can't SELECT
>> from the services tables) would be able to read the large object.
>>
>>
> GRANT SELECT,UPATE ON LARGE OBJECT to user2;
>
> Will this work...
>
> ---
> Regards,
> Raghavendra
> EnterpriseDB Corporation
> Blog: http://raghavt.blogspot.com/
>
>

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

[GENERAL] fsync and wal_sync_method

[ascot.m...@gmail.com]

Hi, 

I am using pg 9.2.4 and ubuntu 12.04 to set up a pair of pg-Master and pg-Slave 
with streaming replication.  

The archive_command is enabled and the "rsync" is used in pg-Master to save all 
its archived WAL files to the 3rd machine for backup purpose,  by default, both 
fsync and wal_sync_method are commented out in postgresql.conf:

archive_command = '(cp %p /usr/pg_arcxlog/%f && rsync -aq  %p 
pg@pg_slave:/usr/pg_arcxlog/%f'
#fsync = on # turns forced 
synchronization on or off
#wal_sync_method = fsync# the default is the first option

Thus I think the latest WAL might not be flushed to disk from time to time in 
pg-Master, therefore the WAL saved in the 3rd server might not contain the 
latest WAL. In order to avoid possible data lost, should 'fsync" be turned ON 
in pg-Master and pg-Slave?  if yes what would be the best settings of 
"wal_sync_method" for these two servers respectively?

Please advise.   
regards

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Odd behavior in functions w/ anyarray & anyelement

[David Johnston]

Joshua Burns wrote
> CREATE OR REPLACE FUNCTION anyel_anyar(anyelement) RETURNS anyarray AS
> $BODY$
> BEGIN
> RETURN $1;
> END;
> $BODY$ LANGUAGE plpgsql;

Two possible interpretations:

1) must return an array of whatever type is supplied; this is (apparently)
the defined behavior
2) must return an array whose base type is the same as the base type of the
input

The first interpretation seems the most useful.  Your last two examples for
this function should indeed fail.

That said I am not really sure why they have to.  In theory "RETURN
anyarray", if paired with an array anyelement, could output/require an array
with one additional dimension compared to the input.  In your example you
should expect something like:

3) ARRAY[ARRAY['one','two']::text[]]::text[][]

I guess this could be considered a third interpretation

So the bigger question is: should PostgreSQL really care?  Option #2 then
makes the fewest assumptions: the base types must match AND the output must
be some form of array.

And the biggest question is whether there are use-cases for the more
complex/flexible behavior so that someone may be enticed to implement it -
and consider the backward compatibility concerns.

Regardless, hopefully this aids your understanding.

David J.






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Odd-behavior-in-functions-w-anyarray-anyelement-tp5770537p5770555.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Odd behavior in functions w/ anyarray & anyelement

[David Johnston]

Joshua Burns wrote
> DROP FUNCTION IF EXISTS anyar_anyel(anyarray);
> CREATE OR REPLACE FUNCTION anyar_anyel(anyarray) RETURNS anyelement AS
> $BODY$
> BEGIN
> RETURN $1;
> END;
> $BODY$ LANGUAGE plpgsql;

Similar to my comment on anyelement->anyarray:

The original goal here is to reduce dimensionality.  In the simple case an
array with a base type is required as input and the output is a scalar
having the same base type.

For both of these the most common usage is to go between 0-dim. and 1-dim.

In theory this limited behavior should probably be restricted to
"anynonarray->anyarray" and vice-versa while the anyelement variations can
allow the more permissive/flexible check of identical base types.

Again, hopefully this is educational.  I'm going from the described behavior
and my general understanding of how things work.  I am not familiar with the
source code nor the design considerations that preceded its creation.

I'm not sure any real conclusions/goals can be drawn absent something more
specific than example queries.  The behavior described (up/down-grading
between 1-dim arrays and scalar values) explains away all your "unexpected"
results.  But your expectations are not unreasonable - just not commonly
used/needed in practice; or if they are someone else has a
solution/work-around I am not familiar with.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Odd-behavior-in-functions-w-anyarray-anyelement-tp5770537p5770556.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] fsync and wal_sync_method

[bricklen]

On Wed, Sep 11, 2013 at 6:11 PM, ascot.m...@gmail.com
wrote:

> Hi,
>
> I am using pg 9.2.4 and ubuntu 12.04 to set up a pair of pg-Master and
> pg-Slave with streaming replication.
>
> The archive_command is enabled and the "rsync" is used in pg-Master to
> save all its archived WAL files to the 3rd machine for backup purpose,  by
> default, both fsync and wal_sync_method are commented out in
> postgresql.conf:
>
> archive_command = '(cp %p /usr/pg_arcxlog/%f && rsync -aq  %p pg@pg_slave
> :/usr/pg_arcxlog/%f'
> #fsync = on # turns forced
> synchronization on or off
> #wal_sync_method = fsync# the default is the first option
>
> Thus I think the latest WAL might not be flushed to disk from time to time
> in pg-Master, therefore the WAL saved in the 3rd server might not contain
> the latest WAL. In order to avoid possible data lost, should 'fsync" be
> turned ON in pg-Master and pg-Slave?  if yes what would be the best
> settings of "wal_sync_method" for these two servers respectively?
>
> Please advise.
> regards
>

I think you are confusing fsync and switching WAL segments for archiving
purposes. Here's a link to the docs to get you started:
http://www.postgresql.org/docs/9.2/static/runtime-config-wal.html#GUC-ARCHIVE-TIMEOUT

fsync is enabled unless you disable it, the fact that it is commented out
means that it is set to its default ("on"). wal_sync_method at its default
is probably fine, but if you are interested, look at the pg_test_fsync
tool: http://www.postgresql.org/docs/9.2/static/pgtestfsync.html

[GENERAL] Best way to populate nested composite type from JSON`

[Chris Travers]

Hi everyone;

I have been starting to experiment with the JSON accessors in 9.3.  I
immediately found that my preferred use, populating nested composite types,
is not supported.  Also of course one cannot manipulate JSON nodes, which
leads to the question of how best to do this.  I had some ideas:

1.  A custom C function utilizing the internal functions PostgreSQL offers
for JSON parsing (probably looking at the json_populate_record() as a base)

2.  Utilizing hstore as an intermediate representation along with
json_each()

3.  Perhaps using something like pl/perlu or the like.

This leads to a few questions, including whether it would be worthwhile
looking at trying to handle nested types in json_populate_record() and if
anyone is working on such a thing currently.

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more.shtml

Re: [GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

[Chris Travers]

Here is a minimal query that demonstrates the problem.  In 9.1 it works:

chris=# select * FROM current_user u join (current_user u cross join
current_user v) x on true;
   u   |   u   |   v
---+---+---
 chris | chris | chris
(1 row)

On 9.3 it fails:
ERROR:  table name "u" specified more than once

It may be a silly example but it works.




On Wed, Sep 11, 2013 at 5:07 PM, Andreas  wrote:

> I took your sample and modified it a bit.
>
> ERROR:  table name "tblb" specified more than once
> Switch tblB and tblC in the subquery and it works.
>
>
> WITH
>   tblA (a_id, a_val) AS ( VALUES (1::integer, 'one-1'::varchar) )
> , tblB (b_id, b_val) AS ( VALUES (1::integer, 'one-b'::varchar) )
> , tblC (c_id, c_val) AS ( VALUES (1::integer, 'one-c'::varchar) )
>
> select  *
> fromtblA
> jointblBon  tblA.a_id = tblB.b_id
> join(
> tblB
> jointblCon  tblC.c_id = tblB.b_id
> )   as  x   on  tblA.a_id = x.c_id;
>
>
>
>
> Am 12.09.2013 00:52, schrieb David Johnston:
>
>  Andreas-3-2 wrote
>>
>>> I never placed a bug-report.  :}
>>> Could you give a hand?
>>>
>> Sure.
>>
>> PostgreSQL homepage (postgresql.org)
>> "Report a Bug" link on right-hand side of page.
>> Fill in the form.
>>
>>  SEND THIS
>

>>  droptable if exists sub_tab;
>>> droptable if exists main_tab;
>>> droptable if exists flag_1;
>>> droptable if exists flag_2;
>>>
>>> create  temporary table flag_1 ( flag_1_id integer primary key, flag_1_t
>>> text );
>>> insert into flag_1 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );
>>>
>>> create  temporary table flag_2 ( flag_2_id integer primary key, flag_2_t
>>> text );
>>> insert into flag_2 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );
>>>
>>> create  temporary table main_tab ( main_id integer primary key, main_t
>>> text, flag_1_id integer references flag_1 ( flag_1_id ) );
>>> insert into main_tab values ( 1, 'Main 1', 1 ), ( 2, 'Main 2', 2 ), ( 3,
>>> 'Main 3', 3 );
>>>
>>> create  temporary table sub_tab ( sub_id integer primary key, sub_t
>>> text, main_id integer references main_tab ( main_id ), flag_1_id integer
>>> references flag_1 ( flag_1_id ), flag_2_id integer references flag_2 (
>>> flag_2_id ) );
>>> insert into sub_tab values ( 1, 'Sub 1', 1, 3, 1 ), ( 2, 'Sub 2', 2, 2,
>>> 2 ), ( 3, 'Sub 3', 3, 1, 3 );
>>>
>>>
>>> select  m.main_id, m.main_t, f.flag_1_t,
>>>   x.sub_id, x.sub_t, x.flag_1_t, x.flag_2_t
>>> frommain_tabas  m
>>>   joinflag_1  as  f   using   ( flag_1_id )
>>>   left join
>>>   (
>>>   sub_tab as  s
>>>   joinflag_2  as  f2  using   ( flag_2_id )   -- that
>>> way it works
>>>   joinflag_1  as  f   using   ( flag_1_id )
>>> --joinflag_2  as  f2  using   ( flag_2_id ) -- that
>>> way it doesn't work
>>>   )   as  x   using   ( main_id );
>>>
>>
>>
>>
>>
>> --
>> View this message in context: http://postgresql.1045698.n5.**
>> nabble.com/PG-9-3-complains-**about-specified-more-than-**
>> once-Those-views-worked-in-PG-**9-1-9-2-tp5770489p5770534.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>
>>
>>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more.shtml