Re: [GENERAL] multiple UNIQUE indices for FK

W dniu 05.03.2016 o 01:03, David G. Johnston pisze: [] > > I've made my point and am not fluent enough to discuss the issues that > would need to be addressed to implement a FK-to-a-partial-unique-index > feature. > > I recall having hit this limitation myself previously so I

Re: [GENERAL] arrays returned in text format

Konstantin Izmailov writes: > Whole point of my question was why PG does not return > binary formatted field when requested (this is a feature supported in the > protocol). You haven't presented a test case demonstrating that that happens in unmodified community source code.

Re: [GENERAL] arrays returned in text format

Tom, that was only a modification for the client-side libpq. The PG is standard, we are using both 9.4 and 9.5 that were officially released. I guess there is no standard test for the scenario. But if such test was created (for checking the format of the returned arrays) it would fail. Maybe I'm

Re: [GENERAL] arrays returned in text format

Konstantin Izmailov writes: > Oops, I forgot to mention that we slightly modified libpq to request > resulting fields formats (since Postgres protocol v3 supports this). Um. I'm not that excited about supporting bugs in modified variants of PG. If you can present a test case

Re: [GENERAL] arrays returned in text format

Oops, I forgot to mention that we slightly modified libpq to request resulting fields formats (since Postgres protocol v3 supports this). See our additions in *Bold*: PQexec(PGconn *conn, const char *query*, int resultFormatCount, const int* resultFormats*) { if (!PQexecStart(conn))

Re: [GENERAL] arrays returned in text format

Konstantin Izmailov writes: > I'm using libpq to read array values, and I noticed that sometimes the > values are returned in Binary and sometimes - in Text format. > 1. Returned in Binary format: >int formats[1] = { 1 }; // request binary format >res = PQexec(conn,

[GENERAL] arrays returned in text format

I'm using libpq to read array values, and I noticed that sometimes the values are returned in Binary and sometimes - in Text format. 1. Returned in Binary format: int formats[1] = { 1 }; // request binary format res = PQexec(conn, "SELECT rgField FROM aTable", 1, formats);

Re: [GENERAL] multiple UNIQUE indices for FK

On Fri, Mar 4, 2016 at 4:17 PM, Rafal Pietrak wrote: > > > > In other words the presence of absence of an FK constraint between two > > tables should not alter the results of any question. But since a > > It wouldn't. > > 3. FK is defined with partially-unique indes. This is

Re: [GENERAL] PLPythonu for production server

On 03/04/2016 01:46 AM, Rémi Cura wrote: Thanks for the answer guys. I should have mentionned that I had read the doc, and was looking for non explicit knowledge, like : - what is the reputation of plpython for a dba? - are there actual production system that use it - what would be the

Re: [GENERAL] multiple UNIQUE indices for FK

W dniu 04.03.2016 o 19:33, David G. Johnston pisze: > On Fri, Mar 4, 2016 at 6:30 AM, Rafal Pietrak >wrote: > > The problem is that once one accepts the requirement for a unique index > as FK target column "mandatory performance support", then

Re: [GENERAL] multiple UNIQUE indices for FK

Hi, W dniu 04.03.2016 o 18:59, Francisco Olarte pisze: [] > > Make sender_person_id NOT NULL in messages if you want to insure every > message ahs exactly ONE SENDER, leave it out if you want to allow > senderless messages. An FK column must either link to a record or be > null.

Re: [GENERAL] query reboot pgsql 9.5.1

David G. Johnston wrote: > ​To be more clear, you need to run a query that will complete in our > lifetime (and without an OOM error) with all three of ANALYZE, BUFFERS, and > TIMING specified for the EXPLAIN. I think the problem is pretty clear. The plan is sensible yet the result doesn't seem

Re: [GENERAL] query reboot pgsql 9.5.1

On Fri, Mar 4, 2016 at 3:09 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > >> 2016-03-04 15:58 GMT-06:00 David G. Johnston >> : >> >>> On Fri, Mar 4, 2016 at 2:56 PM, Felipe de Jesús Molina Bravo < >>> fjmolinabr...@gmail.com> wrote: >>> when i run

Re: [GENERAL] query reboot pgsql 9.5.1

> > > 2016-03-04 15:58 GMT-06:00 David G. Johnston : > >> On Fri, Mar 4, 2016 at 2:56 PM, Felipe de Jesús Molina Bravo < >> fjmolinabr...@gmail.com> wrote: >> >>> when i run without the ANALIZE the output is: >>> >>> pba=# EXPLAIN ( TIMING, BUFFERS) SELECT

Re: [GENERAL] query reboot pgsql 9.5.1

No, these messages are not confuse; I just wanted to show output 2016-03-04 15:58 GMT-06:00 David G. Johnston : > On Fri, Mar 4, 2016 at 2:56 PM, Felipe de Jesús Molina Bravo < > fjmolinabr...@gmail.com> wrote: > >> when i run without the ANALIZE the output is: >> >>

Re: [GENERAL] query reboot pgsql 9.5.1

On Fri, Mar 4, 2016 at 2:56 PM, Felipe de Jesús Molina Bravo < fjmolinabr...@gmail.com> wrote: > when i run without the ANALIZE the output is: > > pba=# EXPLAIN ( TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb a > LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama ); >

Re: [GENERAL] query reboot pgsql 9.5.1

when i run without the ANALIZE the output is: pba=# EXPLAIN ( TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb a LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama ); ERROR: la opción BUFFERS de EXPLAIN requiere ANALYZE pba=# EXPLAIN ( TIMING) SELECT idprodxintegrar FROM

Re: [GENERAL] query reboot pgsql 9.5.1

Felipe de Jesús Molina Bravo wrote: > pba=# \i tablas.sql > DROP TABLE > SELECT 120130 > CREATE INDEX > CREATE INDEX > CREATE INDEX > DROP TABLE > SELECT 91932 > CREATE INDEX > CREATE INDEX > pba=# EXPLAIN (ANALYZE, TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb > a LEFT join _gc_cat b on (

Re: [GENERAL] query reboot pgsql 9.5.1

sorry...i made a mistake...my tables are unlogged and in the last test these was wiped... :( when i create the tables again (with all record) the result is: pba=# \i tablas.sql DROP TABLE SELECT 120130 CREATE INDEX CREATE INDEX CREATE INDEX DROP TABLE SELECT 91932 CREATE INDEX CREATE INDEX pba=#

Re: [GENERAL] query reboot pgsql 9.5.1

On Fri, Mar 4, 2016 at 2:16 PM, Felipe de Jesús Molina Bravo < fjmolinabr...@gmail.com> wrote: > the output is: > > pba=# EXPLAIN (ANALYZE, TIMING, BUFFERS) SELECT idprodxintegrar FROM > _gc_tb a LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama <@ > b.arama ) > ; >

Re: [GENERAL] query reboot pgsql 9.5.1

the output is: pba=# EXPLAIN (ANALYZE, TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb a LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama ) ; QUERY PLAN

Re: [GENERAL] query reboot pgsql 9.5.1

the result was the same: *pba=# ANALYZE VERBOSE public._gc_cat;INFO: analizando «public._gc_cat»INFO: «_gc_cat»: se procesaron 1999 de 1999 páginas, que contenían 91932 filas vigentes y 0 filas no vigentes; 3 filas en la muestra, 91932 total de filas estimadasANALYZEpba=#

Re: [GENERAL] query reboot pgsql 9.5.1

On Fri, Mar 4, 2016 at 1:52 PM, Felipe de Jesús Molina Bravo < fjmolinabr...@gmail.com> wrote: > > >>> Now i execute the same in pgsql 9.4.5 and all is fine!!! >>> >>> The EXPLAINs are: >>> >>> - pgsql 9.5.1: >>> >>>Nested Loop Left Join (cost=0.03..492944.81 rows=276095 width=4) >>> ->

Re: [GENERAL] query reboot pgsql 9.5.1

On Fri, Mar 4, 2016 at 3:52 PM, Felipe de Jesús Molina Bravo < fjmolinabr...@gmail.com> wrote: > > >>> Now i execute the same in pgsql 9.4.5 and all is fine!!! >>> >>> The EXPLAINs are: >>> >>> - pgsql 9.5.1: >>> >>>Nested Loop Left Join (cost=0.03..492944.81 rows=276095 width=4) >>> ->

Re: [GENERAL] query reboot pgsql 9.5.1

> >> Now i execute the same in pgsql 9.4.5 and all is fine!!! >> >> The EXPLAINs are: >> >> - pgsql 9.5.1: >> >>Nested Loop Left Join (cost=0.03..492944.81 rows=276095 width=4) >> -> Seq Scan on _gc_tb a (cost=0.00..3321.30 rows=120130 width=66) >>-> Bitmap Heap Scan on

Re: [GENERAL] query reboot pgsql 9.5.1

On 03/04/2016 12:09 PM, Felipe de Jesús Molina Bravo wrote: Hi!!! I try to explain my problem...sorry for my english :( In pgsql 9.5.1 I have a two tables with the next structure: 1. Tabla unlogged «public._gc_cat» Columna | Tipo | Modificadores

[GENERAL] query reboot pgsql 9.5.1

Hi!!! I try to explain my problem...sorry for my english :( In pgsql 9.5.1 I have a two tables with the next structure: 1. Tabla unlogged «public._gc_cat» Columna | Tipo | Modificadores -+--+--- idppicat| integer |

Re: [GENERAL] multiple UNIQUE indices for FK

On Fri, Mar 4, 2016 at 6:30 AM, Rafal Pietrak wrote: > The problem is that once one accepts the requirement for a unique index > as FK target column "mandatory performance support", then I fail to see > real reazon, where *ENY* unique index shouldn't do that role too. They > are

Re: [GENERAL] String literal doesn't autocast to text type

> On 04 Mar 2016, at 21:08, Tom Lane wrote: > > Alex Ignatov writes: >> Why string literal like 'Hello world!' doesnt automagicaly cast to text >> type? > > Because it's not necessarily a string. It might be meant to be point, > or json, or any

Re: [GENERAL] String literal doesn't autocast to text type

> On 04 Mar 2016, at 20:59, David G. Johnston > wrote: > > It would be nice if you'd follow the list convention of bottom-posting. Not > doing that is much more annoying than the omission of version and o/s - > especially the later since PostgreSQL purports to

Re: [GENERAL] String literal doesn't autocast to text type

Alex Ignatov writes: > Why string literal like 'Hello world!' doesnt automagicaly cast to text > type? Because it's not necessarily a string. It might be meant to be point, or json, or any number of other types. > Sure we can create our cast: > postgres=# create cast

Re: [GENERAL] multiple UNIQUE indices for FK

Hi Rafal: These are my opinions, somebody else may think they are not correct, comments are wellcome. On Fri, Mar 4, 2016 at 2:30 PM, Rafal Pietrak wrote: >> This is normally a sympton of your schema not being appropiately normalized. > OK. I'd apreciate some guidance here.

Re: [GENERAL] String literal doesn't autocast to text type

It would be nice if you'd follow the list convention of bottom-posting. Not doing that is much more annoying than the omission of version and o/s - especially the later since PostgreSQL purports to be generally o/s agnostic. On Fri, Mar 4, 2016 at 12:17 PM, Alex Ignatov

Re: [GENERAL] String literal doesn't autocast to text type

Oh! Great answer! Thats what i want to know!!! Thank you Pavel about explanation!!! Alex Ignatov Postgres Professional: http://www.postgrespro.com Russian Postgres Company > On 04 Mar 2016, at 20:45, Pavel Stehule wrote: > > Hi > > 2016-03-04 18:29 GMT+01:00 Melvin

Re: [GENERAL] String literal doesn't autocast to text type

Hi 2016-03-04 18:29 GMT+01:00 Melvin Davidson : > Probably because pg_typeof() returns the OID of a COLUMN in a table def.. > Strings literals do not have oid's. > no this is not a reason. String literal has fictive "unknown" type. Real type is derivated from context -

Re: [GENERAL] String literal doesn't autocast to text type

Probably because pg_typeof() returns the OID of a COLUMN in a table def.. Strings literals do not have oid's. http://www.postgresql.org/docs/9.4/interactive/functions-info.html "pg_typeof returns the OID of the data type of the value that is passed to it. This can be helpful for troubleshooting

[GENERAL] String literal doesn't autocast to text type

Hello! Why string literal like 'Hello world!' doesnt automagicaly cast to text type? postgres=# select pg_typeof('Hello world'); pg_typeof --- unknown (1 row) But for example literal like 1.1 automagically cast to numeric( not float8, float4, whatever) postgres=# select

Re: [GENERAL] Check constraints for varchar[] and varchar[][] columns in a table

Okay, let's call it <~ :-) On Fri, Mar 4, 2016 at 4:53 PM, Tom Lane wrote: > Alexander Farber writes: > > I am trying to add CHECK constraints to the VARCHAR arrays: > > > hand1 varchar[7] NOT NULL CHECK (ALL(hand1) ~ '^[*A-Z]$'), > > >

Re: [GENERAL] Check constraints for varchar[] and varchar[][] columns in a table

Alexander Farber writes: > I am trying to add CHECK constraints to the VARCHAR arrays: > hand1 varchar[7] NOT NULL CHECK (ALL(hand1) ~ '^[*A-Z]$'), > but get syntax errors near "ALL" > - probably because that keyword is supposed to be on the right side?

Re: [GENERAL] Check constraints for varchar[] and varchar[][] columns in a table

On Fri, Mar 4, 2016 at 6:59 AM, Alexander Farber wrote: > Hello, > > for a Scrabble-like word game using PostgreSQL 9.5 as backend > I am trying to add CHECK constraints to the VARCHAR arrays: > > CREATE TABLE words_games ( > gid SERIAL PRIMARY KEY, >

[GENERAL] Slave-Master replication on top of BDR

Hi. I currently have two servers in different geographical locations; both of them are replicating with Postgres-BDR, that's OK. However, I need two more servers to get a read only replication of only some tables from the master ones. At first I tried with Slony, but it just didn't work(don't

[GENERAL] Check constraints for varchar[] and varchar[][] columns in a table

Hello, for a Scrabble-like word game using PostgreSQL 9.5 as backend I am trying to add CHECK constraints to the VARCHAR arrays: CREATE TABLE words_games ( gid SERIAL PRIMARY KEY, created timestamptz NOT NULL, player1 integer REFERENCES words_users(uid) ON DELETE CASCADE

Re: [GENERAL] Re: could not migrate 8.0.13 database with large object data to 9.5.1

Wow -- you have an old db version there! When doing a little research, I found that back in 2005 you actually had the same basic issue - that the way you were using the "lo" contrib module in 7.x and 8.0 was not supported in 8.1 anymore:

Re: [GENERAL] pg_upgrade 9.5.1: pg_upgrade_support missing

Hello, I tried to delete all binary_upgrade schematas using this: $ psql -tc "SELECT datname FROM pg_database" -U postgres -p 5433 | xargs -I {} psql -d {} -U postgres -p 5433 -tc "drop schema if exists binary_upgrade cascade;" But got this: output is not a tty So I got the dblist from pgadmin

Re: [GENERAL] CStringGetTextDatum and other conversions in server-side code

all the text types are simply a 32bit length and an array of characters. you need to be aware of the encoding, and null is just another character. [dmb>] Yes, I can see that. What I need is a function that will convert to and from whatever the actual encoding happens to be into Unicode,

Re: [GENERAL] multiple UNIQUE indices for FK

Hi, W dniu 04.03.2016 o 12:59, Francisco Olarte pisze: > Hi Rafal: > > On Fri, Mar 4, 2016 at 11:44 AM, Rafal Pietrak wrote: >> While doing so I fell onto another problem, to which I cannot find any >> resolve so far. > ... >> 2. but in the original schema I did have an

Re: [GENERAL] C function migration from 9.2 to 9.5

Nailed it. Ensuring heap_form_tuple was properly defined resolved the issue. Thanks a lot. Without your help, I would probably have spent more time looking at the 32 <=> 64 bit thing first, since I was pretty certain that we had this warning in the old compile. I suspect I am wrong about that,

Re: [GENERAL] multiple UNIQUE indices for FK

Hi Rafal: On Fri, Mar 4, 2016 at 11:44 AM, Rafal Pietrak wrote: > While doing so I fell onto another problem, to which I cannot find any > resolve so far. ... > 2. but in the original schema I did have an additional field NEXT, which > allowed me to dasy-chain all messages

Re: [GENERAL] multiple UNIQUE indices for FK

Hmmm... aparently, it takes more time to rewrite schema+app to the new layout :( While doing so I fell onto another problem, to which I cannot find any resolve so far. 1. partial index asuring ssn unique for sender work fine. 2. but in the original schema I did have an additional field NEXT,

Re: [GENERAL] space required before negative

On 3 March 2016 at 17:30, Tom Lane wrote: > That's a syntax issue, so the place to look is > http://www.postgresql.org/docs/9.5/static/sql-syntax-lexical.html#SQL-SYNTAX-OPERATORS Ah, thanks. Perhaps a note in the operators page might be helpful? I'll change to use <> in

Re: [GENERAL] PLPythonu for production server

On 4 March 2016 at 10:46, Rémi Cura wrote: > Thanks for the answer guys. > > I should have mentionned that I had read the doc, > and was looking for non explicit knowledge, > like : > - what is the reputation of plpython for a dba? > Dunno. - are there actual production

Re: [GENERAL] Export binary data - PostgreSQL 9.2

-b/--blobs option should be required when making selective dumps. I just wanted to note for the sake of completeness. Have a nice day. On Thu, Mar 3, 2016 at 3:50 AM, Abdul Sayeed wrote: > Hi, > > You can use pg_dump with -t and -Fc option to take dump of a table in >

Re: [GENERAL] bloated postgres data folder, clean up

​Hey Julien, thanks for the original query ! ​ There was a slight mistake in the query, it was comparing the file name with pg_class.relfilenode. It is not safe in some case (see doc : "caution" in here ) , so better use the

Re: [GENERAL] PLPythonu for production server

Thanks for the answer guys. I should have mentionned that I had read the doc, and was looking for non explicit knowledge, like : - what is the reputation of plpython for a dba? - are there actual production system that use it - what would be the recommended usage perimeter ? (only