Re: [GENERAL] Fields re-ordered on JOIN with * and USING

[Ken Tanzer]

>
> ...least excruciating version of the relevant text...


Ouch, I'm glad you folks take care of reading such stuff!  What you put in
the documentation was much much clearer--just wish I had found it!

Speaking of which, I had looked at the "From" section of the "SELECT" page (
https://www.postgresql.org/docs/current/static/sql-select.html#SQL-FROM),
which also has a somewhat detailed section on joins.  I'm wondering about
the utility of:


   - Adding a link from the "SELECT" page (FROM section) to the page Tom
   referenced (which includes a link the other way)

and/or

   - Adding this detail to the section on USING on the select page:


A clause of the form USING ( a, b, ... ) is shorthand for ON left_table.a =
right_table.a AND left_table.b = right_table.b  Also, USING implies
that only one of each pair of equivalent columns will be included in the
join output, not both.  *Outcome columns specified by USING will appear
first in the joined results.*

Cheers,
Ken

>
>

Re: [GENERAL] Fields re-ordered on JOIN with * and USING

[Tom Lane]

... btw, a little digging shows that this ordering is required by the
SQL standard.  The least excruciating version of the relevant text is
in SQL92 7.5 :

d) Let SLCC be a  of s of the form

 COALESCE ( TA.C, TB.C ) AS C

  for every column C that is a corresponding join column, taken
  in order of their ordinal positions in T1.

e) Let SL1 be a  of those s of T1
  that are not corresponding join columns, taken in order of
  their ordinal positions in T1, and let SLT2 be a  of those s of T2 that are not correspond-
  ing join columns, taken in order of their ordinal positions
  in T2.

f) The descriptors of the columns of the result of the  are the same as the descriptors of the columns of the
  result of

 SELECT SLCC, SLT1, SLT2 FROM TR1, TR2


Later versions of the standard use many more words to say the same thing.

regards, tom lane


-- 
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] Fields re-ordered on JOIN with * and USING

[Tom Lane]

"David G. Johnston"  writes:
> On Fri, Sep 1, 2017 at 2:25 PM, Ken Tanzer  wrote:
>> Hi.  I recently noticed that when doing a SELECT * with USING, that the
>> join field(s) appear first in the output.  I'd never noticed that before,
>> and was just curious if that is expected behavior or not.  Thanks.

> ​I don't recall if or where it is documented but it is intentional., as is
> the documented fact that only one instance of the named column appears in
> the output.

Yes, it's documented, here:

https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-FROM

under "7.2.1.1. Joined Tables":

Furthermore, the output of JOIN USING suppresses redundant columns:
there is no need to print both of the matched columns, since they must
have equal values. While JOIN ON produces all columns from T1 followed
by all columns from T2, JOIN USING produces one output column for each
of the listed column pairs (in the listed order), followed by any
remaining columns from T1, followed by any remaining columns from T2.

regards, tom lane


-- 
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] Fields re-ordered on JOIN with * and USING

[David G. Johnston]

On Fri, Sep 1, 2017 at 2:25 PM, Ken Tanzer  wrote:

> Hi.  I recently noticed that when doing a SELECT * with USING, that the
> join field(s) appear first in the output.  I'd never noticed that before,
> and was just curious if that is expected behavior or not.  Thanks.
>

​I don't recall if or where it is documented but it is intentional., as is
the documented fact that only one instance of the named column appears in
the output.

David J.

Re: [GENERAL] Strange SQL result - any ideas.

[Tom Lane]

Paul Linehan  writes:
> I have a table (fred) that I want to transform into JSON and
> I use the following command (ignore the backslash stuff):
> ...
> which is fine (note that the field "mary" is sorted correctly) but
> I want "proper" JSON - i.e. with open and close square brackets
> i.e. ([ - ]) before and after the fields!

Well, proper JSON would also require commas between the array elements,
no?  I think what you're really after is

=# SELECT json_agg(ROW_TO_JSON(t)) 
FROM
(   
  SELECT * FROM fred
  ORDER BY mary, jimmy, paulie
) AS t;
json_agg

 
-
 [{"mary":2,"jimmy":43,"paulie":"asfasfasfd"}, 
{"mary":3,"jimmy":435,"paulie":"ererere"}, 
{"mary":3,"jimmy":44545,"paulie":"\\sdfs\\sfsgf"}, 
{"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}, 
{"mary":35,"jimmy":5,"paulie":"wrew\\sdfsd"}]
(1 row)

As far as that UNION query goes, I think you misunderstand
what UNION does.  It doesn't promise to preserve ordering.
You might have gotten the results you expected with UNION
ALL (but they still wouldn't have constituted a valid
JSON array).

regards, tom lane


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

[GENERAL] Strange SQL result - any ideas.

[Paul Linehan]

I have a table (fred) that I want to transform into JSON and
I use the following command (ignore the backslash stuff):

SELECT REGEXP_REPLACE(ROW_TO_JSON(t)::TEXT, '', '\\', 'g')
FROM
(
  SELECT * FROM fred
  ORDER BY mary, jimmy, paulie
) AS t;

which gives

regexp_replace
--
 {"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
 {"mary":3,"jimmy":435,"paulie":"ererere"}
 {"mary":3,"jimmy":44545,"paulie":"\sdfs\\\sfs\\gf"}
 {"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
 {"mary":35,"jimmy":5,"paulie":"wrew\sdfsd"}
(5 rows)


which is fine (note that the field "mary" is sorted correctly) but
I want "proper" JSON - i.e. with open and close square brackets
i.e. ([ - ]) before and after the fields!

So, I tried this query:

SELECT '[' AS my_data  -- <<-- added line
UNION  -- <<-- added line
SELECT REGEXP_REPLACE(ROW_TO_JSON(t)::TEXT, '', '\\', 'g')
FROM
(
  SELECT * FROM fred
  ORDER BY mary, jimmy, paulie
) AS t
UNION   -- <<-- added line
SELECT ']';-- <<-- added line

*_BUT_*, this gives


   my_data
--
 ]
 [
 {"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
 {"mary":35,"jimmy":5,"paulie":"wrew\sdfsd"}
 {"mary":3,"jimmy":44545,"paulie":"\sdfs\\\sfs\\gf"}
 {"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
 {"mary":3,"jimmy":435,"paulie":"ererere"}
(7 rows)


Two problems with this  result - one is that my square brackets are not in
the right place - this at least I understand - the first character of
each line is sorted by its ASCII value - '[' comes before ']' (naturally)
and '{' comes after them both - or have I got that right?

But, I do *_not_* understand why my table data is now out
of sort order - I've looked at it and can't see *_how_* the sort
order in my table data has been determined.

Anybody got any logical explanations as to what's going on?

TIA & Rgs,


Paul...


-- CREATE TABLE and INSERT INTO statements.


CREATE TABLE fred (
mary integer PRIMARY KEY,
jimmy integer,
paulie character varying(20)
);

INSERT INTO fred (mary, jimmy, paulie) VALUES (2, 43, 'asfasfasfd');
INSERT INTO fred (mary, jimmy, paulie) VALUES (3, 435, 'ererere');
INSERT INTO fred (mary, jimmy, paulie) VALUES (6, 43343, 'eresdfssfsfasfae');
INSERT INTO fred (mary, jimmy, paulie) VALUES (35, 5, 'wrew\sdfsd');
INSERT INTO fred (mary, jimmy, paulie) VALUES (3, 44545, '\sdfs\\\sfs\\gf');


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

[GENERAL] Fields re-ordered on JOIN with * and USING

[Ken Tanzer]

Hi.  I recently noticed that when doing a SELECT * with USING, that the
join field(s) appear first in the output.  I'd never noticed that before,
and was just curious if that is expected behavior or not.  Thanks.

Ken

CREATE TEMP TABLE t1 (
  f1 INTEGER,
  f2 INTEGER UNIQUE,
  f3 INTEGER,
  f4 INTEGER
);

CREATE TEMP TABLE t2 (
  f2 INTEGER,
  f3 INTEGER,
  f5 INTEGER
);

SELECT * FROM t1 LEFT JOIN t2 USING (f3,f2);

 f3 | f2 | f1 | f4 | f5
++++
(0 rows)



-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] Issue with json_agg() and ordering

[Bob Jones]

>>
>> I guess that the order by should be in the aggregation.
>>
>> SELECT json_agg(a.* ORDER BY a.last_name, a.last_year DESC)
>> FROM my_table a;
>
> yes.  however, you would say, json_agg(a... not 'a.*').  The .*
> notation only works in certain contexts, and is transformed at parse
> time to, a.col1, a.col2, a.col3...  which would not work inside an
> aggregation function which can only handle a single column or record.
>
> merlin


Awesome ! Thanks Charles for the answer, and Merlin for the
tweaking/additional insight.


-- 
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] pglogical bidirectional replication of sequences

[Peter J. Holzer]

On 2017-09-01 10:29:51 +0200, Peter J. Holzer wrote:
> pglogical supports replication of sequences, and although the way it
> does this suggests that it can't really work in both directions
> (actually I'm sceptical that it works reliably in one direction), of
> course I had to try it.
> 
> So I created a sequence on both nodes and called
> select pglogical.replication_set_add_sequence('default', 'test_sequence');
> on both nodes.
> 
> The result was ... interesting.
> 
> First I got the same sequence (1, 2, 3, 4, 5) on both nodes.
> 
> After a few seconds the replication kicked in, and then I got the same
> value (1005) on both nodes most of the time, with a few variants (2005,
> 3005) thrown in.
> 
> In a word, the sequence was completely unusable.

[...some failed attempts to recover...]

> So, is there a way to recover from this situation without drastic
> measures like nuking the whole database.

To answer my own question:

delete from pglogical.queue where message_type='S';
on both nodes seems to have the desired effect.
A vacuum full pglogical.queue afterwards is a good idea to get the
bloated table back to a reasonable size.

hp



-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: Digital signature

Re: [GENERAL] Issue with json_agg() and ordering

[Merlin Moncure]

On Fri, Sep 1, 2017 at 6:22 AM, Charles Clavadetscher
 wrote:
> Hello
>
>> -Original Message-
>> From: pgsql-general-ow...@postgresql.org 
>> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bob Jones
>> Sent: Freitag, 1. September 2017 10:12
>> To: pgsql-general 
>> Subject: [GENERAL] Issue with json_agg() and ordering
>>
>> Hi,
>>
>>
>> Could anyone give me a few pointers as to how I might resolve the following :
>>
>> select json_agg(my_table) from (my_table) where foo='test' and bar='f'
>> order by last_name asc, first_name asc;
>>
>> ERROR:  column "my_table.last_name" must appear in the GROUP BY clause or be 
>> used in an aggregate function LINE 1:
>> ...foo='f' order by last_name ...
>
> I guess that the order by should be in the aggregation.
>
> SELECT json_agg(a.* ORDER BY a.last_name, a.last_year DESC)
> FROM my_table a;

yes.  however, you would say, json_agg(a... not 'a.*').  The .*
notation only works in certain contexts, and is transformed at parse
time to, a.col1, a.col2, a.col3...  which would not work inside an
aggregation function which can only handle a single column or record.

merlin


-- 
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] pglogical bidirectional replication of sequences

[Peter J. Holzer]

On 2017-09-01 09:57:52 -0600, Rob Sargent wrote:
> On 09/01/2017 02:29 AM, Peter J. Holzer wrote:
> >TLDR: Don't.
> >
> >I'm currently conducting tests which should eventually lead to a 2 node
> >cluster with working bidirectional logical replication.
> >
> >(Postgresql 9.6.4-1.pgdg90+1, pglogical 2.0.1-1.jessie+1 on Debian 9
> >(Stretch))
> >
> >pglogical supports replication of sequences, and although the way it
> >does this suggests that it can't really work in both directions
> >(actually I'm sceptical that it works reliably in one direction), of
> >course I had to try it.
> >
[and it blew up]

> I trust you mean don't use sequences

I trust you don't mean what I understood ;-).

Seriously:

Sequences in general are fine and very useful. I think they should be
used where appropriate.

Sequences and logical replication don't mix well. That still doesn't
mean that you can't use sequences, you just have to be careful how you
use them. 

Since replicating sequence state doesn't really work, I think it is best
to use independent sequences on each node and just configure them in a
way that they can not produce the same values. A naive approach would be
to use MINVALUE/MAXVALUE/START WITH to ensure non-overlapping ranges. A
somewhat more elegant approach is to increment by $n$ (the number of
nodes in the cluster) and use different start values (I got that idea
from
http://thedumbtechguy.blogspot.co.at/2017/04/demystifying-pglogical-tutorial.html).
 

There are other ways to get unique ids: A uuid should work pretty well
in most cases, and in some even a random 64 bit int might be enough.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: Digital signature

Re: [GENERAL] pglogical bidirectional replication of sequences

[Rob Sargent]

On 09/01/2017 02:29 AM, Peter J. Holzer wrote:

TLDR: Don't.

I'm currently conducting tests which should eventually lead to a 2 node
cluster with working bidirectional logical replication.

(Postgresql 9.6.4-1.pgdg90+1, pglogical 2.0.1-1.jessie+1 on Debian 9
(Stretch))

pglogical supports replication of sequences, and although the way it
does this suggests that it can't really work in both directions
(actually I'm sceptical that it works reliably in one direction), of
course I had to try it.

So I created a sequence on both nodes and called
select pglogical.replication_set_add_sequence('default', 'test_sequence');
on both nodes.

The result was ... interesting.

First I got the same sequence (1, 2, 3, 4, 5) on both nodes.

After a few seconds the replication kicked in, and then I got the same
value (1005) on both nodes most of the time, with a few variants (2005,
3005) thrown in.

In a word, the sequence was completely unusable.

Experiment completed, so I removed the sequence from the replication
set:

select pglogical.replication_set_remove_sequence('default', 'test_sequence');
on both nodes.

But the behaviour of the sequence doesn't change. It still returns 1005
most of the time, and sometimes 2005 or 3005. This is true even after
restarting both nodes.

Plus, I can't drop the sequence any more (as the user who created the
sequence):

wds=> drop sequence public.test_sequence ;
ERROR:  permission denied for schema pglogical

So, clearly, pglogical is still managing that sequence.

If I drop the sequence as postgres and then recreate it, it works
normally for some time (also the sequence on the other node now works
normally), but after some time, the replication kicks in again and the
sequence is stuck again at 1005.

So, is there a way to recover from this situation without drastic
measures like nuking the whole database.

 hp

I trust you mean don't use sequences



--
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] Issue with json_agg() and ordering

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bob Jones
> Sent: Freitag, 1. September 2017 10:12
> To: pgsql-general 
> Subject: [GENERAL] Issue with json_agg() and ordering
> 
> Hi,
> 
> 
> Could anyone give me a few pointers as to how I might resolve the following :
> 
> select json_agg(my_table) from (my_table) where foo='test' and bar='f'
> order by last_name asc, first_name asc;
> 
> ERROR:  column "my_table.last_name" must appear in the GROUP BY clause or be 
> used in an aggregate function LINE 1:
> ...foo='f' order by last_name ...

I guess that the order by should be in the aggregation.

SELECT json_agg(a.* ORDER BY a.last_name, a.last_year DESC)
FROM my_table a;

Regards
Charles

> 
> 
> I suspect It doesn't really matter what my table looks like for the purposes 
> of the above, but if you need something
> to go by:
> 
> create table my_table(
> last_name text,
> first name text,
> foo text,
> bar boolean
> );
> 
> Thanks !
> 
> Bob
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



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

[GENERAL] pglogical bidirectional replication of sequences

[Peter J. Holzer]

TLDR: Don't.

I'm currently conducting tests which should eventually lead to a 2 node
cluster with working bidirectional logical replication.

(Postgresql 9.6.4-1.pgdg90+1, pglogical 2.0.1-1.jessie+1 on Debian 9
(Stretch))

pglogical supports replication of sequences, and although the way it
does this suggests that it can't really work in both directions
(actually I'm sceptical that it works reliably in one direction), of
course I had to try it.

So I created a sequence on both nodes and called
select pglogical.replication_set_add_sequence('default', 'test_sequence');
on both nodes.

The result was ... interesting.

First I got the same sequence (1, 2, 3, 4, 5) on both nodes.

After a few seconds the replication kicked in, and then I got the same
value (1005) on both nodes most of the time, with a few variants (2005,
3005) thrown in.

In a word, the sequence was completely unusable.

Experiment completed, so I removed the sequence from the replication
set:

select pglogical.replication_set_remove_sequence('default', 'test_sequence');
on both nodes.

But the behaviour of the sequence doesn't change. It still returns 1005
most of the time, and sometimes 2005 or 3005. This is true even after
restarting both nodes. 

Plus, I can't drop the sequence any more (as the user who created the
sequence):

wds=> drop sequence public.test_sequence ;
ERROR:  permission denied for schema pglogical

So, clearly, pglogical is still managing that sequence.

If I drop the sequence as postgres and then recreate it, it works
normally for some time (also the sequence on the other node now works
normally), but after some time, the replication kicks in again and the
sequence is stuck again at 1005. 

So, is there a way to recover from this situation without drastic
measures like nuking the whole database.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: Digital signature

[GENERAL] Issue with json_agg() and ordering

[Bob Jones]

Hi,


Could anyone give me a few pointers as to how I might resolve the following :

select json_agg(my_table) from (my_table) where foo='test' and bar='f'
order by last_name asc, first_name asc;

ERROR:  column "my_table.last_name" must appear in the GROUP BY clause
or be used in an aggregate function
LINE 1: ...foo='f' order by last_name ...


I suspect It doesn't really matter what my table looks like for the
purposes of the above, but if you need something to go by:

create table my_table(
last_name text,
first name text,
foo text,
bar boolean
);

Thanks !

Bob


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