[GENERAL] cloning database

Hello, I need around 150 copies of a database (for an exam). I have got a database with tables and data and for my exam I would copy this database in his way: database_source database1 database2 …. database150 Is there a buildin way to clone the "database_source" with all structure and dat

[GENERAL] Reserved keywords and qualified identifiers

Hi, http://dev.mysql.com/doc/refman/5.7/en/reserved-words.html explicitly states that qualified identifiers do not have to be quoted even if they are reserved keywords ("A word that follows a period in a qualified name must be an identifier, so it need not be quoted even if it is reserved"). http

Re: [GENERAL] 64-bit TXID?

On Thu, Sep 18, 2014 at 9:17 PM, cowwoc wrote: > I did a quick search and noticed that 8 years ago, a Skype employee provided > a patch for migrating to a 64-bit TXID: > http://postgresql.1045698.n5.nabble.com/RFC-txid-module-for-64-bit-external-transaction-IDs-tt1947503.html Actually, this patch

[GENERAL] 64-bit TXID?

Hi, A recent discussion in Slashdot brought up the topic of PostgreSQL's 32-bit TXID system: http://slashdot.org/comments.pl?sid=5725497&cid=47942637 I did a quick search and noticed that 8 years ago, a Skype employee provided a patch for migrating to a 64-bit TXID: http://postgresql.1045698.n5.n

Re: [GENERAL] Watching Views

Ah! Your reply was excellent, David. I only found it now, cleaning out my inbox. Comments inline! Quoting David G Johnston : Nick Guenther wrote As you said, attaching the trigger to a view is useless (for BEFORE/AFTER, which I'm interested in, also only works on statement level changes, w

Re: [GENERAL] [SQL] pg_multixact issues

Dev Kumkar wrote: > On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund > wrote: > > Can you show pg_controldata output and the output of 'SELECT oid, > > datname, relfrozenxid, age(relfrozenxid), relminmxid FROM pg_database;'? > > > > Here are the details: > oid datname datfrozenxidag

Re: [GENERAL] Re: How can i monitor exactly what (partition) tables are accessed by a query?

Thanks, David. I have read that page many times but clearly I have forgotten this: - Constraint exclusion only works when the query's WHERE clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such asCURRENT_TIMEST

Re: [GENERAL] How can i monitor exactly what (partition) tables are accessed by a query?

Joy, The explain plan shows that all partitions will be scanned but i believe that plan isn't valid because the check constraint that dictates which partition to access can't be known until the query is executed due to the value being a join. You can see what i mean using the SQL below. I have no

[GENERAL] Re: How can i monitor exactly what (partition) tables are accessed by a query?

Robert Nix wrote > I'm experiencing a problem with queries apparently not using the check > constraints of my partition tables (tried constraint_exclusion =partition > and =on with same results) and explain isn't sufficient to diagnose the > issue because the value for the check constraint in the q

Re: [GENERAL] How can i monitor exactly what (partition) tables are accessed by a query?

Jov blog: http:amutu.com/blog 2014-09-19 2:44 GMT+08:00 Robert Nix : > I'm experiencing a problem with queries apparently not using the check > constraints of my partition tables (tried constraint_exclusion =partition > and =on with same results) and explain isn't sufficie

Re: [GENERAL] How to run a second instance on the same server?

On 9/18/2014 6:14 PM, Andreas wrote: I've got 2 hardware servers running PG 9.3.5 from the postgres.org-repository. It's a Ubuntu server 12.x. Box 2 replictes box 1. That works. I'd like to use Box 2 as a Test- and Training installation as it is probaply bored to death just mirroring Box 1.

[GENERAL] How to run a second instance on the same server?

Hi, I've got 2 hardware servers running PG 9.3.5 from the postgres.org-repository. It's a Ubuntu server 12.x. Box 2 replictes box 1. That works. I'd like to use Box 2 as a Test- and Training installation as it is probaply bored to death just mirroring Box 1. AFAIK I can't just create an addi

[GENERAL] a couple questions about convert()

Hello, In a postgresql-9.3.1 database with UTF8 encoding I can do: select convert_from (E'\\x68656c6c6f', 'LATIN1'); convert_from -- hello But when I explicitly give the "to" encoding: select convert (E'\\x68656c6c6f', 'LATIN1', 'UTF8'); convert -

Re: [GENERAL] [SQL] pg_multixact issues

On 09/18/2014 10:22 AM, Dev Kumkar wrote: On Thu, Sep 18, 2014 at 6:20 PM, Dev Kumkar mailto:devdas.kum...@gmail.com>> wrote: On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund mailto:and...@2ndquadrant.com>> wrote: I don't think that's relevant for you. Did you upgrade the

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 18-09-2014 17:58, cowwoc wrote: On 18/09/2014 4:26 PM, David G Johnston [via PostgreSQL] wrote: ​ "only PostgreSQL uses it" ... PostgreSQL doesn't use Java. I don't think it makes a difference from a licensing point of view. The point is that the JRE is not used to run multiple distinct

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On Wed, Sep 17, 2014 at 11:42 PM, cowwoc wrote: > Tom, > > For starters, let's talk strictly about improving the deployment situation, > which the core team *is* uniquely positioned to do. > > The pl/java author(s) should figure out what needs to be done but once > that's known we need to do *some

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 18/09/2014 4:26 PM, David G Johnston [via PostgreSQL] wrote: > ​ "only PostgreSQL uses it" ... PostgreSQL doesn't use Java. I don't think it makes a difference from a licensing point of view. The point is that the JRE is not used to run multiple distinct applications. > You want PostgreSQL to

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 09/18/2014 03:26 PM, David G Johnston wrote: There is a lot that can be done in this area but someone - and not the core developers - needs to champion the cause; providing or asking for specific core enhancements to be made as integration problems arise. Then help the various packagers creat

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On Thu, Sep 18, 2014 at 4:00 PM, cowwoc [via PostgreSQL] < ml-node+s1045698n5819545...@n5.nabble.com> wrote: > Guy, > > As far as I understand, the concerns you brought up only apply to a public > JRE. > > A private JRE is no different than any other library Postgresql links > against. It's an imp

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 09/17/2014 11:30 PM, Tom Lane wrote: But here's the *key* reason: if pl/java is failing to stay afloat as an external project, that is a terrible reason for pulling it into core. It suggests strongly that the manpower or interest to keep it going simply doesn't exist. This is really all th

Re: [GENERAL] Why isn't Java support part of Postgresql core?

Guy, As far as I understand, the concerns you brought up only apply to a public JRE. A private JRE is no different than any other library Postgresql links against. It's an implementation detail that does not affect your system-wide applications. Your vulnerability is no greater using an outda

Re: [GENERAL] strange problem with not existing roles

On 09/18/2014 09:44 AM, lud...@kni-online.de wrote: Hi Adrian, this database runs as develop-version on my PC and was created by hand, no dumps or pg_upgrade. The same database runs as production-version on another server (PostgreSQL 9.3.1, compiled by Visual C++ build 1600, 32-bit), so far witho

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 9/18/2014 2:44 PM, cowwoc wrote: Yes, that's what I meant. I just wanted to reinforce the fact that you don't need to bundle multiple JVMs (Oracle, OpenJDK and GCJ). You'd pick one and bundle it alongside PG and pl/java. I've been following along as an interested observer, having used PL/Jav

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 18/09/2014 3:17 PM, John R Pierce [via PostgreSQL] wrote: > On 9/18/2014 11:44 AM, cowwoc wrote: > > > > Yes, that's what I meant. I just wanted to reinforce the fact that you > > don't need to bundle multiple JVMs (Oracle, OpenJDK and GCJ). You'd > pick > > one and bundle it alongside PG and p

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 9/18/2014 11:44 AM, cowwoc wrote: Yes, that's what I meant. I just wanted to reinforce the fact that you don't need to bundle multiple JVMs (Oracle, OpenJDK and GCJ). You'd pick one and bundle it alongside PG and pl/java. I think a lawyer would have to pick apart the JRE redistribution lice

[GENERAL] How can i monitor exactly what (partition) tables are accessed by a query?

I'm experiencing a problem with queries apparently not using the check constraints of my partition tables (tried constraint_exclusion =partition and =on with same results) and explain isn't sufficient to diagnose the issue because the value for the check constraint in the query comes from a join co

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 18/09/2014 2:21 PM, John R Pierce [via PostgreSQL] wrote: > > Right, so to recap: each platform will only need one jvm.dll/so library > > (which you would update over time). You don't need to include one > > version for Oracle JDK, OpenJDK, GCJ. You'd pick one, and bundle its > > jvm.dll (I'd su

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 9/18/2014 11:06 AM, cowwoc wrote: On 18/09/2014 1:31 PM, John R Pierce [via PostgreSQL] wrote: > On 9/18/2014 10:17 AM, Szymon Guz wrote: > > Does it mean that there should be distributed many jvm.dlls for each > > of the jvm versions? > > jvm.dll is part of the jre, its not a standalone libra

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 18/09/2014 1:31 PM, John R Pierce [via PostgreSQL] wrote: > On 9/18/2014 10:17 AM, Szymon Guz wrote: > > Does it mean that there should be distributed many jvm.dlls for each > > of the jvm versions? > > jvm.dll is part of the jre, its not a standalone library. Right, so to recap: each platform

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 9/18/2014 10:17 AM, Szymon Guz wrote: Does it mean that there should be distributed many jvm.dlls for each of the jvm versions? jvm.dll is part of the jre, its not a standalone library. -- john r pierce 37N 122W somewhere on the middle of the left coas

Re: [GENERAL] [SQL] pg_multixact issues

On Thu, Sep 18, 2014 at 6:20 PM, Dev Kumkar wrote: > On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund > wrote: > >> I don't think that's relevant for you. >> >> Did you upgrade the database using pg_upgrade? >> > > That's correct! No, there is no upgrade here. > > >> Can you show pg_controldata ou

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 18 September 2014 18:54, John R Pierce wrote: > On 9/18/2014 9:07 AM, cowwoc wrote: > >> You are confusing a private JRE installation with the public JRE >> installation (yes, there is such a thing). The public JRE is found in >> java/jre8. The private JRE is found in java/jdk1.8.0_20/jre. >>

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 9/18/2014 9:07 AM, cowwoc wrote: You are confusing a private JRE installation with the public JRE installation (yes, there is such a thing). The public JRE is found in java/jre8. The private JRE is found in java/jdk1.8.0_20/jre. http://www.oracle.com/technetwork/java/javase/jdk-8-readme-209

Re: [GENERAL] strange problem with not existing roles

Hi Adrian, this database runs as develop-version on my PC and was created by hand, no dumps or pg_upgrade.   The same database runs as production-version on another server (PostgreSQL 9.3.1, compiled by Visual C++ build 1600, 32-bit), so far without these problems.   pgAdmin shows a mix of th

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 18/09/2014 9:18 AM, David G Johnston [via PostgreSQL] wrote: > What distributions of JRE are available on the Windows platform and > which ones are allowed to be "privately distributed"? (Answered in a follow-up email to John Pierce) > For this scenario your target audience is the people, at

Re: [GENERAL] Why isn't Java support part of Postgresql core?

John, You are confusing a private JRE installation with the public JRE installation (yes, there is such a thing). The public JRE is found in java/jre8. The private JRE is found in java/jdk1.8.0_20/jre. http://www.oracle.com/technetwork/java/javase/jdk-8-readme-2095712.html#redistribution give

Re: [GENERAL] I want the stupidest possible binary export

Jov writes: > psql can only input／output text string，which can not be binary content。with > 9.2，you can encode bytea to base64，save to file，then use shell command to > decode the file。 This worked, btw. Encoded to base64, piped to sed to fix the newlines, piped to 'base64 -id' and then to file.

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 18 September 2014 17:32, John R Pierce wrote: > On 9/18/2014 6:18 AM, David G Johnston wrote: > >> What distributions of JRE are available on the Windows platform and which >> ones are allowed to be "privately distributed"? >> > > afaik, Sun/Oracle is /the/ Java for Windows, and AFAIK, it does

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 9/18/2014 6:18 AM, David G Johnston wrote: What distributions of JRE are available on the Windows platform and which ones are allowed to be "privately distributed"? afaik, Sun/Oracle is /the/ Java for Windows, and AFAIK, it does NOT allow redistribution, Oracle wants you to register each us

Re: [GENERAL] I want the stupidest possible binary export

I use php and pg_unescape_bytea  http://php.net/manual/en/function.pg-unescape-bytea.php You also need to set bytea format to escaped in front of your query.  If php can be en option /Nicklas Avén Skickat från min Samsung Mobil. Originalmeddelande Från: Jov Datum:2014-

Re: [GENERAL] I want the stupidest possible binary export

Jov writes: > psql can only input／output text string，which can not be binary content。with > 9.2，you can encode bytea to base64，save to file，then use shell command to > decode the file。 > google “amutu.com pg bytea” can get a blog post。 I wondered if I could do that. OK, will try it, thanks. smi

Re: [GENERAL] I want the stupidest possible binary export

Adrian Klaver writes: >> psql -t -c "\copy (select mybinaryfield from mytable where key = 1) to >> 'file'" with format binary > > From here: > > http://www.postgresql.org/docs/9.2/static/app-psql.html > > the above should be: > > psql -t -c "\copy (select mybinaryfield from mytable wher

Re: [GENERAL] I want the stupidest possible binary export

psql can only input／output text string，which can not be binary content。with 9.2，you can encode bytea to base64，save to file，then use shell command to decode the file。 google “amutu.com pg bytea” can get a blog post。 with upcoming 9.4，you can change bytea to large object，then use lo_* psql cmd save

Re: [GENERAL] I want the stupidest possible binary export

On 09/18/2014 07:06 AM, David Rysdam wrote: I've got a some tables with bytea fields that I want to export only the binary data to files. (Each field has a gzipped data file.) I really want to avoid adding overhead to my project by writing a special program to do this, so I'm trying to do it fro

Re: [GENERAL] I want the stupidest possible binary export

On 18 September 2014 16:06, David Rysdam wrote: > I've got a some tables with bytea fields that I want to export only the > binary data to files. (Each field has a gzipped data file.) > > I really want to avoid adding overhead to my project by writing a > special program to do this, so I'm trying

[GENERAL] I want the stupidest possible binary export

I've got a some tables with bytea fields that I want to export only the binary data to files. (Each field has a gzipped data file.) I really want to avoid adding overhead to my project by writing a special program to do this, so I'm trying to do it from psql. Omitting the obvious switches for user

Re: [GENERAL] strange problem with not existing roles

On 09/18/2014 04:12 AM, lud...@kni-online.de wrote: Hi Adrian, data got into the database with normal update/insert-queries from logged-in database-users using "normal" PG-Users/roles, the "ghost-roles" (with these unusual numerical role-names) were never created by me, I don't know where they co

Re: [GENERAL] Why isn't Java support part of Postgresql core?

cowwoc wrote > Chris, > > On 18/09/2014 1:07 AM, Chris Travers wrote: >> On Wed, Sep 17, 2014 at 9:42 PM, cowwoc < > cowwoc@.darktech > > > cowwoc@.darktech > >> wrote: >> >> Tom, >> >> For starters, let's talk strictly about improving the deployment >> situation, whic

Re: [GENERAL] [SQL] pg_multixact issues

On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund wrote: > I don't think that's relevant for you. > > Did you upgrade the database using pg_upgrade? > That's correct! No, there is no upgrade here. > Can you show pg_controldata output and the output of 'SELECT oid, > datname, relfrozenxid, age(rel

Re: [GENERAL] strange problem with not existing roles

Hi Adrian, data got into the database with normal update/insert-queries from logged-in database-users using "normal" PG-Users/roles, the "ghost-roles" (with these unusual numerical role-names) were never created by me, I don't know where they come from.   The query [SNIP] SELECT * FROM pg_ca

Re: [GENERAL] [SQL] pg_multixact issues

On 2014-09-18 14:41:07 +0530, Dev Kumkar wrote: > On Thu, Sep 18, 2014 at 2:41 AM, Adrian Klaver > wrote: > > > > > Aaah, hit enter too soon. Also see the other changes under Changes that > > apply to multixact in 9.3.5 > > > Thanks for sharing same. Found this one interesting "Truncate pg_mult

Re: [GENERAL] [SQL] pg_multixact issues

On Thu, Sep 18, 2014 at 2:41 AM, Adrian Klaver wrote: > > Aaah, hit enter too soon. Also see the other changes under Changes that > apply to multixact in 9.3.5 Thanks for sharing same. Found this one interesting "Truncate pg_multixact during checkpoints, not during VACUUM (Álvaro Herrera)" and

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On Thursday, September 18, 2014, cowwoc wrote: > Chris, > > On 18/09/2014 1:07 AM, Chris Travers wrote: > > On Wed, Sep 17, 2014 at 9:42 PM, cowwoc > wrote: > >> Tom, >> >> For starters, let's talk strictly about improving the deployment >> situation, which the core team *is* uniquely positione