Re: [GENERAL] looking for a globally unique row ID

W dniu 18.09.2017 o 17:26, Jehan-Guillaume (ioguix) de Rorthais pisze: > On Sat, 16 Sep 2017 12:15:46 +0200 > Rafal Pietrak <ra...@ztk-rp.eu> wrote: > >> W dniu 16.09.2017 o 09:33, Jehan-Guillaume de Rorthais pisze: [-] > > About FK, just look at pgsql

Re: [GENERAL] looking for a globally unique row ID

W dniu 17.09.2017 o 03:30, Gmail pisze: > > >> On Sep 16, 2017, at 11:18 AM, Rafal Pietrak <ra...@ztk-rp.eu> wrote: >> >> Dear robjsarg...@gmail.com, >> >> W dniu 16.09.2017 o 17:19, Gmail pisze: >>> >>> >>>

Re: [GENERAL] looking for a globally unique row ID

W dniu 16.09.2017 o 22:20, rob stone pisze: > > > On Sat, 2017-09-16 at 19:18 +0200, Rafal Pietrak wrote: >> Dear robjsarg...@gmail.com, [-] >> >> > Hello Rafal, Hi, > > > I've been trying to follow this discussion but now I'm tot

Re: [GENERAL] looking for a globally unique row ID

Dear robjsarg...@gmail.com, W dniu 16.09.2017 o 17:19, Gmail pisze: > > >> On Sep 16, 2017, at 8:23 AM, Rafal Pietrak <ra...@ztk-rp.eu> wrote: >> >> >> >> W dniu 16.09.2017 o 15:45, Adam Brusselback pisze: >>> Here is the last discussion I saw

Re: [GENERAL] looking for a globally unique row ID

W dniu 16.09.2017 o 15:45, Adam Brusselback pisze: > Here is the last discussion I saw on > it: > https://www.postgresql.org/message-id/flat/90261791-b731-a516-ab2a-dafb97df4464%40postgrespro.ru#90261791-b731-a516-ab2a-dafb97df4...@postgrespro.ru >

Re: [GENERAL] looking for a globally unique row ID

W dniu 16.09.2017 o 12:43, Tomas Vondra pisze: > On 09/16/2017 11:57 AM, Rafal Pietrak wrote: >> >> >> I'M NOT POSTGRES HACKER. THIS IS JUST NA INTUITION. >> >> >> W dniu 15.09.2017 o 21:30, Christopher Browne pisze: >>> On 15 September 2

Re: [GENERAL] looking for a globally unique row ID

W dniu 16.09.2017 o 09:33, Jehan-Guillaume de Rorthais pisze: > [-] > > I wrote something about this some years ago, this might do the trick for you, > maybe with some adjustments depending on your schema. The main idea should > help anyway. See: > >

Re: [GENERAL] looking for a globally unique row ID

I'M NOT POSTGRES HACKER. THIS IS JUST NA INTUITION. W dniu 15.09.2017 o 21:30, Christopher Browne pisze: > On 15 September 2017 at 14:45, Adam Brusselback > wrote: [---] > > b) Referencing which index entries can be dropped (e.g. - vacuumed out) >

Re: [GENERAL] looking for a globally unique row ID

W dniu 15.09.2017 o 21:30, Christopher Browne pisze: > On 15 September 2017 at 14:45, Adam Brusselback > wrote: >>> I cannot image a single postgres index covering more than one physical >>> table. Are you really asking for that? >> >> >> While not available yet, that

Re: [GENERAL] looking for a globally unique row ID

W dniu 15.09.2017 o 20:49, Rob Sargent pisze: > > > On 09/15/2017 12:45 PM, Adam Brusselback wrote: >> >> I cannot image a single postgres index covering more than one >> physical table. Are you really asking for that? >> >> >> While not available yet, that is a feature that has had

Re: [GENERAL] looking for a globally unique row ID

W dniu 15.09.2017 o 13:36, Alban Hertroys pisze: > On 15 September 2017 at 11:03, Rafal Pietrak <ra...@ztk-rp.eu> wrote: > [-] > > For example, if we define: > create table master_table ( > year int > , month int > , example text

Re: [GENERAL] looking for a globally unique row ID

W dniu 14.09.2017 o 23:15, Rob Sargent pisze: > > > On 09/14/2017 02:39 PM, Rafal Pietrak wrote: >> >> W dniu 14.09.2017 o 19:30, Rob Sargent pisze: >>> >>> On 09/14/2017 11:11 AM, Rafal Pietrak wrote: [--] >> >> Throwin

Re: [GENERAL] looking for a globally unique row ID

W dniu 14.09.2017 o 19:30, Rob Sargent pisze: > > > On 09/14/2017 11:11 AM, Rafal Pietrak wrote: >> [--] >> So I'm stuck with seriously incomplete solution. >> >> that's why I have an impression, that I'm going into entirely wrong >&

Re: [GENERAL] looking for a globally unique row ID

W dniu 14.09.2017 o 15:54, Merlin Moncure pisze: > On Thu, Sep 14, 2017 at 2:45 AM, Rafal Pietrak <ra...@ztk-rp.eu> wrote: >> Hello everybody, >> >> Can anybody help me find a way to implement an ID which: >> >> 1. guarantees being unique across m

Re: [GENERAL] looking for a globally unique row ID

W dniu 14.09.2017 o 10:57, George Neuner pisze: > On Thu, 14 Sep 2017 09:45:59 +0200, Rafal Pietrak <ra...@ztk-rp.eu> > wrote: > >> Hello everybody, >> >> Can anybody help me find a way to implement an ID which: >> >> 1. guarantees being unique ac

[GENERAL] looking for a globally unique row ID

Hello everybody, Can anybody help me find a way to implement an ID which: 1. guarantees being unique across multiple tables. 2. guarantees its uniqueness not only during INSERT, but also during the lifetime of the database/application (e.i. during future UPDATES). 3. guarantees persistence of

Re: [GENERAL] PG vs ElasticSearch for Logs

W dniu 19.08.2016 o 10:57, Thomas Güttler pisze: > > > Am 19.08.2016 um 09:42 schrieb John R Pierce: [-] >> in fact thats several rows/second on a 24/7 basis > > There is no need to store them more then 6 weeks in my current use case. > > I think indexing in postgres is much

Re: [GENERAL] Debian and Postgres

W dniu 04.05.2016 o 22:55, rob stone pisze: [--] > > I can connect via psql and issue queries without any problems. Trying > to connect via JDBC fails. Trying to connect by an application fails. > Since psql works, have you tried the basic tests to figure out the

Re: [GENERAL] Why are data files stored in /var/lib

W dniu 30.04.2016 o 05:45, Manuel Gómez pisze: > On Fri, Apr 29, 2016 at 11:00 PM, Charles Clavadetscher > wrote: >> I had a discussion yesterday with some friends, who are sysadmins about the >> location of database files. In a default installation >> from a

Re: [GENERAL] multiple UNIQUE indices for FK

W dniu 07.03.2016 o 20:11, Francisco Olarte pisze: [---] > > When in a single process problem like this I normally use an > second timestamp which I autoincrement if repeated, something > like: > > get_timestamp_for_id() { > Locked(mutex) { > now=time(); > if

Re: [GENERAL] multiple UNIQUE indices for FK

W dniu 05.03.2016 o 19:53, Francisco Olarte pisze: > Hi Rafal: > > On Fri, Mar 4, 2016 at 11:46 PM, Rafal Pietrak <ra...@ztk-rp.eu> wrote: >> W dniu 04.03.2016 o 18:59, Francisco Olarte pisze: >>> Make sender_person_id NOT NULL in messages if you want to insure eve

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] 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 <ra...@ztk-rp.eu > <mailto:ra...@ztk-rp.eu>>wrote: > > The problem is that once one accepts the requirement for a unique index > as FK target column &qu

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] 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 <ra...@ztk-rp.eu> wrote: >> While doing so I fell onto another problem, to which I cannot find any >> resolve so far. > ... >> 2. b

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] multiple UNIQUE indices for FK

W dniu 01.03.2016 o 20:02, Igor Neyman pisze: [] > > > It just occured to me: how do I make sure (e.g. force within a database) with > the above structure, that a message can have *only one* sender? > but, allow for multiple recepients? > > -R > >

Re: [GENERAL] multiple UNIQUE indices for FK

W dniu 28.02.2016 o 03:35, David G. Johnston pisze: > W dniu 23.02.2016 o 09:39, Rafal Pietrak pisze: > > Can anybody suggest any other way out of this mass? > > > ​The only thought that sticks while reading your prose is:​ > > ​message >

Re: [GENERAL] multiple UNIQUE indices for FK

W dniu 28.02.2016 o 03:35, David G. Johnston pisze: > W dniu 23.02.2016 o 09:39, Rafal Pietrak pisze: > > Can anybody suggest any other way out of this mass? > > > ​The only thought that sticks while reading your prose is:​ > > ​message >

Re: [GENERAL] multiple UNIQUE indices for FK

te to another. So my question is: is there a way to point (using foreign key) a record from MOST_RECENT table, into a record within MSGS table? (provided the uniqueness within MSGS is assured only partially depending on FROMME - as shown above)? any help appreciated. -R W dniu 23.02.2016 o 09:39, Rafal Piet

[GENERAL] multiple UNIQUE indices for FK

Hi, For some time I'm struggling to get my schema "optimised" for a sort of "message exchange" (or "document circulation") system. For every record in the table of those messages I have: 1. SENDER 2. RECEIPIENT 3. unique (sender assigned)SN 4. ... and naturally all the other stuff, like the

Re: [GENERAL] partial JOIN (was: ID column naming convention)

W dniu 26.10.2015 o 01:49, Jim Nasby pisze: > On 10/25/15 2:30 AM, Rafal Pietrak wrote: [] > But there's also times I've wanted a way to manipulate what * would > normally do. In particular, *_except_for(field_list), and *_replace( > regexp_replace to run on eac

Re: [GENERAL] partial JOIN (was: ID column naming convention)

W dniu 24.10.2015 o 23:25, Dane Foster pisze: > > On Sat, Oct 24, 2015 at 5:23 PM, Dane Foster > wrote: > [] > -- > Sent via pgsql-general mailing list > (pgsql-general@postgresql.org

Re: [GENERAL] partial JOIN (was: ID column naming convention)

W dniu 24.10.2015 o 21:03, Rafal Pietrak pisze: > > > W dniu 24.10.2015 o 15:00, David G. Johnston pisze: >> On Sat, Oct 24, 2015 at 6:41 AM, Rafal Pietrak <ra...@ztk-rp.eu >> <mailto:ra...@ztk-rp.eu>>wrote: > [--] >> >>

Re: [GENERAL] partial JOIN (was: ID column naming convention)

W dniu 24.10.2015 o 15:00, David G. Johnston pisze: > On Sat, Oct 24, 2015 at 6:41 AM, Rafal Pietrak <ra...@ztk-rp.eu > <mailto:ra...@ztk-rp.eu>>wrote: [--] > > ​Using explicit column names is expected - using "*" in non-trivial and > pr

[GENERAL] partial JOIN (was: ID column naming convention)

Hi, Recent interesting discussion on the list, on (just) naming convention reminded me of a related problem which I haven't resolved myself, jet. As slowly I learn SQL (like a blind dog in a meat market), currently I've just started to use table JOINS more extensively ... and I often bump on a

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

Franscisco, W dniu 21.07.2015 o 09:34, Francisco Olarte pisze: Hi Rafal: On Mon, Jul 20, 2015 at 3:33 PM, Rafal Pietrak ra...@ztk-rp.eu wrote: Regarding the last point. Usually, I implement one-time used vouchers as rows in table like: CREATE TABLE (voucher int not null, consumed bool

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

If I'm not mistaken, the conclusions from posts in this thread are: 1. recognizing of a RETRY action, as a separate case of ON CONFLICT transaction continuation is not generally appreciated. 2. I shouldn't expect any hidden corruption/performance obstacles when simply re-attempting of an INSERT

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

Hi, W dniu 19.07.2015 o 09:33, Charles Clavadetscher pisze: [---] 2. with current (as of 9.5) implementation I think I can always ON CONFLICT DO NOTHING, and retry the INSERT from application level. An UPSERT is try an INSERT and if there is a conflict, do nothing or UPDATE

[GENERAL] INSERT ... ON CONFLICT DO UPDATE

Hello, I've just started to read through postgres-9.5 what's new ... before giving it a try. The insert ... on conflict do update is particularly atractive to me; but I was wondering why it does not cover the third usage scenario of action that a programmer may need for a PK conflict during

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

Hi, W dniu 19.07.2015 o 10:27, Geoff Winkless pisze: On 19 July 2015 at 09:11, Rafal Pietrak ra...@ztk-rp.eu mailto:ra...@ztk-rp.eu wrote: I'm not particularly fond of using functions to accessing RDBMS instead of tables. And I'm not particularly fond of workarounds. Use

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

Hi, W dniu 19.07.2015 o 14:10, Geoff Winkless pisze: On 19 July 2015 at 11:30, Rafal Pietrak ra...@ztk-rp.eu mailto:ra...@ztk-rp.eu wrote: when I have to invent/introduce additional features/columns/attributes (like a key in addition to a sequence), which are not required

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

W dniu 19.07.2015 o 16:33, Adrian Klaver pisze: On 07/19/2015 06:47 AM, Rafal Pietrak wrote: Hi, W dniu 19.07.2015 o 14:10, Geoff Winkless pisze: On 19 July 2015 at 11:30, Rafal Pietrak ra...@ztk-rp.eu [---] Although a random can duplicate its previous values, my random(s

Re: [GENERAL] LDAP Authentication

W dniu 23.04.2015 o 00:06, John R Pierce pisze: On 4/22/2015 2:57 PM, Joseph Kregloh wrote: I see. That would still require a manual process to create the user on each server. I was planing on using some already existing scripts to create the user automatically on all servers and then LDAP

Re: [GENERAL] partial on-delete set null constraint

W dniu 03.02.2015 o 04:44, Jim Nasby pisze: On 1/3/15 2:49 AM, Rafal Pietrak wrote: [---] But an application could do -a successfull scenario with expected result--- testvm=# UPDATE mailboxes SET username = null; UPDATE 1 testvm=# DELETE FROM

Re: [GENERAL] partial on-delete set null constraint

W dniu 04.01.2015 o 02:02, Alban Hertroys pisze: [--] A table can have at most one primary key, but it may have more than one candidate key. A primary key is a combination of columns which uniquely specify a row; it is a special case of unique keys. One difference

Re: [GENERAL] partial on-delete set null constraint

W dniu 03.01.2015 o 14:11, Alban Hertroys pisze: [--] You assumed a functional dependency between username and domain, while those fields actually describe independent entities that don’t necessarily go together as you found out. Hence you need to normalise further. For

Re: [GENERAL] partial on-delete set null constraint

W dniu 03.01.2015 o 16:07, Adrian Klaver pisze: On 01/03/2015 12:49 AM, Rafal Pietrak wrote: [-] With TRIGGER alone (i.e. without documenting FK), one will have to analize the body of an ever growing function. Which at certain point would become too much of an effort

Re: [GENERAL] partial on-delete set null constraint

A supporting view works OK. - CREATE VIEW api2users AS SELECT * from mailusers; CREATE or replace RULE remove_user AS ON DELETE TO api2users do instead (update mailboxes set username=null where username=old.username and domain=old.domain; delete from mailusers where

Re: [GENERAL] partial on-delete set null constraint

W dniu 03.01.2015 o 16:48, Alban Hertroys pisze: On 03 Jan 2015, at 15:20, Rafal Pietrak ra...@ztk-rp.eu wrote: [-] Yes. This is precisely the semantics I'm trying to put into the schema: after a username is released from service, all it's messages become from

Re: [GENERAL] partial on-delete set null constraint

W dniu 02.01.2015 o 20:37, Adrian Klaver pisze: On 01/02/2015 08:55 AM, Rafal Pietrak wrote: [--] Is there a way to forcebly push the old.username=null, throughout the on-update FK chains into the all dependent tables, before constraints are checked for DELETE operation? I

[GENERAL] partial on-delete set null constraint

Hello, Rewriting my mail-hub I fell into the following problem: 1. I have a table with mailmessages, which has an FK to a table of hub users. 2. I'd like to retain the content of message repository (with it's domain key not cleared), when I drop a particular username from service to

Re: [GENERAL] partial on-delete set null constraint

W dniu 02.01.2015 o 17:05, Adrian Klaver pisze: On 01/02/2015 07:45 AM, Rafal Pietrak wrote: W dniu 02.01.2015 o 16:03, Adrian Klaver pisze: On 01/02/2015 04:31 AM, Rafal Pietrak wrote: [] CCing the list. Ups, sorry - not that button clicked

[GENERAL] sefety of passwords for web-service applications

Hello, I'm analysing a way to avoid one of the password attack vectors for web services, which goes like this: 1. acquire passwords database (assuming passwords are hashed) 2. run cracking software on the hashes as long as you like. Obviously the attack is more difficult if the step-1 is made as

Re: [GENERAL] sefety of passwords for web-service applications

On Sat, 2012-11-24 at 01:41 -0800, Chris Travers wrote: [-] I think the closest you can come are the new security barriers in views. Yes. This is actually what I'm currently thinking to use. A security definer function, invoked within a WITH clausure of a VIEW. -R --

[GENERAL] enter/leave session triggers

Hi, Have there been any discussion on the list on triggers fires when new session (libpg session to server?) is opened/closed? Somehow I cannot google anything with the keywords I use. And the question is: has postgresql any plans to have: 1. triggers that fire BEFORE/AFTER user opens a session

Re: [GENERAL] RFE: Column aliases in WHERE clauses

On Sat, 2012-09-22 at 20:00 -0700, Chris Travers wrote: On Sat, Sep 22, 2012 at 12:23 AM, Jasen Betts ja...@xnet.co.nz wrote: On 2012-09-18, Rafal Pietrak ra...@zorro.isa-geek.com wrote: [-] could be written by user as (3): SELECT 1/x AS inverse

Re: [GENERAL] foreign key from array element

On Tue, 2012-09-18 at 09:37 +0200, Albe Laurenz wrote: Rafal Pietrak wrote: On Mon, 2012-09-17 at 14:31 -0500, Merlin Moncure wrote: On Mon, Sep 17, 2012 at 7:24 AM, Rafal Pietrak ra...@zorro.isa-geek.com wrote: [] Who can review that patch? You :^) I did what I

Re: [GENERAL] RFE: Column aliases in WHERE clauses

On Wed, 2012-09-19 at 20:36 +0800, Craig Ringer wrote: On 09/19/2012 02:36 PM, Steve Haresnape wrote: Since some dialects support the idiom and others don't there can't be any compelling reason to withhold support. It's really a matter of style. For my money the DRY style is better. So

Re: [GENERAL] foreign key from array element

On Tue, 2012-09-18 at 18:39 -0400, Tom Lane wrote: Rafal Pietrak ra...@zorro.isa-geek.com writes: postmaster/postmaster.o: In function `PostmasterMain':^M postmaster.c:(.text+0x4bc8): undefined reference to `optreset'^M tcop/postgres.o: In function `process_postgres_switches':^M

Re: [GENERAL] foreign key from array element

On Mon, 2012-09-17 at 14:31 -0500, Merlin Moncure wrote: On Mon, Sep 17, 2012 at 7:24 AM, Rafal Pietrak ra...@zorro.isa-geek.com wrote: On Mon, 2012-09-17 at 19:58 +0800, Craig Ringer wrote: On 09/17/2012 04:46 PM, Rafal Pietrak wrote: [--] There was some quite recent

Re: [GENERAL] RFE: Column aliases in WHERE clauses

On Mon, 2012-09-17 at 16:44 -0700, Mike Christensen wrote: On Mon, Sep 17, 2012 at 4:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: Mike Christensen m...@kitchenpc.com writes: [---] It's easier to understand why this is if you realize that SQL has a very clear model of a pipeline of

Re: [GENERAL] foreign key from array element

On Tue, 2012-09-18 at 09:37 +0200, Albe Laurenz wrote: Rafal Pietrak wrote: [--] Who can review that patch? You :^) ;7 OK. (still smiling a little) [---] Here is information about what is required: http://wiki.postgresql.org/wiki/Reviewing_a_Patch I

Re: [GENERAL] foreign key from array element

Hi, On Tue, 2012-09-18 at 15:12 +0200, Gabriele Bartolini wrote: Hi Rafal, [] It is such a coincidence that yesterday I had started to write this article (http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/) about this feature for 9.3 and

Re: [GENERAL] foreign key from array element

On Tue, 2012-09-18 at 15:38 -0500, Kevin Grittner wrote: Rafal Pietrak ra...@zorro.isa-geek.com wrote: where do I get current-v9.3 from? At this point 9.3 just means the HEAD of the master branch of the git repository, which is where development for the next major release of software

[GENERAL] foreign key from array element

Hello the list, recently I'm cooking a database for an application, that I feel is best implemented with tables containing arrays. I have found some related info here: http://archives.postgresql.org/pgsql-hackers/2008-09/msg01356.php although the initial advice in that thread was: not to use

Re: [GENERAL] foreign key from array element

On Mon, 2012-09-17 at 19:58 +0800, Craig Ringer wrote: On 09/17/2012 04:46 PM, Rafal Pietrak wrote: [--] There was some quite recent discussion on ELEMENT foreign keys on the -hackers list. Try searching pgsql-hackers for ELEMENT foreign key. See: https

Re: [GENERAL] Can't figure out how to use now() in default for tsrange column (PG 9.2)

On Mon, 2012-07-16 at 14:08 -0400, David Johnston wrote: [] Specific, but unknown (e.g., day of week, month, year, etc...) results could return NaN though NULL is also, probably more, reasonable given the context. The goal would be to use Infinity in case where comparisons are

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

On Sat, 2012-06-23 at 12:18 +, Jasen Betts wrote: On 2012-06-19, Rafal Pietrak ra...@zorro.isa-geek.com wrote: And we are talking about interractive psql breaking transaction because of syntax error - almost always this is a one time typo. I'd prefere it to be a bit more sloopy

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

On Wed, 2012-06-20 at 00:24 -0700, Chris Travers wrote: [--] I guess it seems to me that I would not object to a new option for transaction behavior where one could do something like SET TRANSACTION INTERACTIVE; and have no errors abort the transaction at all (explicit commit

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

On Tue, 2012-06-19 at 19:06 +0800, Craig Ringer wrote: On 06/19/2012 02:20 PM, Tom Lane wrote: So you're suggesting that SELECT 1/0; should terminate a transaction, but SELECT 1//0; should not? How about ROLBACK;? It gets pretty squishy pretty fast when you try to decide which sorts of

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

On Wed, 2012-06-20 at 08:27 +1200, Gavin Flower wrote: [] I would be be extremely concerned about any move to allow syntax errors not to abort a transaction. Me too. But it's a nuicence for interractive session when transactions breakes due to syntax error - still,

Re: [GENERAL] how to make an SQL UPDATE from record returning function

On Tue, 2012-04-24 at 12:10 +0200, Thomas Kellerer wrote: Rafal Pietrak, 24.04.2012 09:02: is not an option, since the function is *very* expensive (multiple join of large tables - inventories, history, etc). Is there a syntax workaround that I could possibly use to get the effect

[GENERAL] how to make an SQL UPDATE from record returning function

Hi all, Recently I have fell onto a multicolumn update problem, earlier discussed here: http://postgresql.1045698.n5.nabble.com/UPDATE-of-several-columns-using-SELECT-statement-td1916045.html But in my case, subselect does not help, since in my case, new values for a row I get from an output of

Re: [GENERAL] how to make an SQL UPDATE from record returning function

On Tue, 2012-04-24 at 02:48 -0500, Abel Abraham Camarillo Ojeda wrote: [] Why don't create table my_table which stores the composite value by itself (not in two parts)? Hmmm. OK. mea coulpa. I didn't follow the SQL good practice, and I don't have a unique ID column in

[GENERAL] a dumb question regarding RULES

Hi all, I've been using RULES for some time now, and despite the fact, that I always had to make experiments to have the expected results, it worked for me just fine. Now I have this simple scenario: --- mbr2=# CREATE TEMP TABLE test(a int, b

Re: [GENERAL] a dumb question regarding RULES

On Mon, 2011-10-10 at 16:48 +0200, hubert depesz lubaczewski wrote: On Mon, Oct 10, 2011 at 04:06:34PM +0200, Rafal Pietrak wrote: Hi all, first of all - why did you send this mail as reply to some 2-weeks old thread, instead of just start of new thread? Sorry for that. Old habits

[GENERAL] why VOLATILE attribute is required?

Hi All, I have this function: CREATE FUNCTION mypass(newpass text) returns text EXECUTE 'ALTER USER ' || quote_ident(session_user) || ' PASSWORD ' || quote_literal(newpass); return session_user::text; to varify user passwords before allowing a change. I've put that function in a RULE that

Re: [GENERAL] why VOLATILE attribute is required?

On Thu, 2011-09-22 at 07:50 -0500, Merlin Moncure wrote: On Thu, Sep 22, 2011 at 5:28 AM, Rafal Pietrak ra...@zorro.isa-geek.com wrote: if you change the state of the database, including (and especially) system catalogs, your function is volatile, period. Hmmm. To quote from the online

Re: [GENERAL] why VOLATILE attribute is required?

On Thu, 2011-09-22 at 16:57 +0200, Szymon Guz wrote: On 22 September 2011 16:29, Rafal Pietrak ra...@zorro.isa-geek.com wrote: [--] Well. In this caase, I'd like it being optimised away. This is the expected result. And the above documentation

[GENERAL] INSERT-colision/MERGE in postgresql

Hi, I've recently looked into the problem of my INSERTs throwing an ROW error, when a new row hits an already present one, by unique constraint. It triggers an expensive rollback, and I'd like to have it sort of optimised. In my case, duplicates can be discarded on an attempt INSERT, but an

Re: [GENERAL] An aggregate function on ARRAY

On Wed, 2010-08-11 at 09:53 -0400, Merlin Moncure wrote: On Wed, Aug 11, 2010 at 8:42 AM, Rafal Pietrak ra...@zorro.isa-geek.com wrote: [] SELECT min(A[1]) as a1, min(A[2]) as a2, ... This is because aggregate functions are not defined on ARRAY types. Or may be there is an easier

[GENERAL] An aggregate function on ARRAY

Hi, I've started using ARRAY data type recently, and I fell into the following problem: When I have a relatively large ARRAY (like [1:500]) takeing an aggregate function on its elements is not so easy. One has to iterate all the elements, like this: SELECT min(A[1]) as a1, min(A[2]) as a2, ...

[GENERAL] sequences and RULEs

Hi All! I've seen sometning unexpected here. I'd apreciate it if someone could give me a hint of why this have happened and may be a sugesstion of a workaround. I'm writing Building Access Control System (BACS). My environment is Debian testing with their current postgresql version: 8.3.7. I my

Re: [GENERAL] sequences and RULEs

On Fri, 2009-06-05 at 14:32 +0100, Richard Huxton wrote: Rafal Pietrak wrote: The NEW tuple of the table EVENTLOG, in its ID field at the moment of RULE execution has a value of 5! But after everything is finished, the actual value deposited in that record is 4. A rule rewrites the query

[GENERAL] INSERT .... RETURNING

Hi Everybody, Forgive my sarcasm below, but I just *adore* postgres for years, now. I hope it's all natural with this level of emotions to be deeply hurt when the object of attraction is (to quote HHTTG by Douglas Adams): almost, but not quite entirely unlike tea. I've just upgraded to

Re: [GENERAL] INSERT .... RETURNING

Hi, On Wed, 2008-11-05 at 10:34 -0500, Merlin Moncure wrote: On Wed, Nov 5, 2008 at 2:41 AM, Rafal Pietrak [EMAIL PROTECTED] wrote: Hi Everybody, [...] I've just upgraded to v8.3.4 ... since eventually it does have INSERT ... RETURNING extention to the SQL standard. The documentation

Re: [GENERAL] INSERT .... RETURNING

On Wed, 2008-11-05 at 14:38 -0500, Merlin Moncure wrote: [] It's more complicated than it looks (triggers). Could you give me pointers where I could get some more information on [] matter, the system has several not-easily-removed assumptions that a SELECT command won't fire

Re: [GENERAL] a performence question

be immensely simpler. And thenx again for the help in evaluating the routing performance. This helped me a lot! -R On Fri, 2008-09-05 at 17:04 +0200, Filip Rembiałkowski wrote: 2008/9/4 Rafal Pietrak [EMAIL PROTECTED]: Hi, Maybe someone on this list actually have already tried

[GENERAL] a performence question

Hi, Maybe someone on this list actually have already tried this: I'm planning to make a partitioned database. From Postgres documentation I can see, that there are basically two methods to route INSERTS into partitioned table: one. is a TRIGGER other. is a RULE My Table will

Re: [GENERAL] a JOIN on same table, but 'slided over'

Thank you All for this extensive help! BTW: google helps, once you know that the construct is called correlated subquery - there is no way to get an answer before one knows the question :) Thenx again! -R On Thu, 2007-06-28 at 23:23 +0530, Gurjeet Singh wrote: On 6/28/07, Alban Hertroys

Re: [GENERAL] a JOIN on same table, but 'slided over'

] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com 17°29'34.37N 78°30'59.76E - Hyderabad * 18°32'57.25N 73°56' 25.42 E - Pune Sent from my BlackLaptop device On 6/26/07, Rafal Pietrak [EMAIL PROTECTED] wrote

[GENERAL] a JOIN on same table, but 'slided over'

Hi, I understand, that this is 'general SQL' question rather then 'general postgres'. But may be someone here could help me with it anyways. I have a *single* table: CREATE TABLE test (id int not null unique, thread int not null, info text); The ID, although unique, is not continues. A sample

Re: [GENERAL] a JOIN on same table, but 'slided over'

Marvelous! Thenx! -R On Tue, 2007-06-26 at 10:06 +0200, hubert depesz lubaczewski wrote: On 6/26/07, Rafal Pietrak [EMAIL PROTECTED] wrote: Is there an SQL construct to get it? select distinct on (t1.id) t1.*, t2.* from test t1 join test t2 on t2.id t1.id

Re: [GENERAL] a JOIN on same table, but 'slided over'

* 18°32'57.25N 73°56'25.42 E - Pune Sent from my BlackLaptop device On 6/26/07, Rafal Pietrak [EMAIL PROTECTED] wrote: Marvelous! Thenx! -R On Tue, 2007-06-26 at 10:06 +0200, hubert depesz lubaczewski wrote: On 6/26/07, Rafal

Re: [GENERAL] a JOIN on same table, but 'slided over'

: canceling statement due to user request postgres=# On 6/26/07, Rafal Pietrak [EMAIL PROTECTED] wrote: OK. Have tried this one looks like close to 6 times slower then the 'non-standard' phrase with 'distinct on'. On the small dataset

Re: [GENERAL] TRIGGER BEFORE INSERT

On Tue, 2007-01-09 at 18:41 +0100, Rafal Pietrak wrote: On Tue, 2007-01-09 at 10:44 -0500, Tom Lane wrote: Rafal Pietrak [EMAIL PROTECTED] writes: 1. either the new value of test_days.dnia as already present in the NEW row, is not visible to UPDATE test_utarg sub-statement of the same

Re: [GENERAL] TRIGGER BEFORE INSERT

On Thu, 2007-01-11 at 15:10 +0100, Alban Hertroys wrote: Rafal Pietrak wrote: Hi! I'm re-posting this message again in hope someone would have a look at the case again. .. it's pending. You were given a solution; defer the foreign key constraint. Well. I were, but probably I'm

Re: [GENERAL] TRIGGER BEFORE INSERT

Perfect! Thenx! -R On Thu, 2007-01-11 at 12:26 -0800, Adrian Klaver wrote: On Thursday 11 January 2007 10:26 am, Rafal Pietrak wrote: On Thu, 2007-01-11 at 15:10 +0100, Alban Hertroys wrote: Rafal Pietrak wrote: Hi! I'm re-posting this message again in hope someone would have

Re: [GENERAL] TRIGGER BEFORE INSERT

On Tue, 2007-01-09 at 10:44 -0500, Tom Lane wrote: Rafal Pietrak [EMAIL PROTECTED] writes: 1. either the new value of test_days.dnia as already present in the NEW row, is not visible to UPDATE test_utarg sub-statement of the same transaction. But earlier versions of Postgres did allow

  1   2   >