Re: [GENERAL] select md5 result set

[Jeff Janes]

On Wed, Aug 2, 2017 at 4:25 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Aug 2, 2017 at 3:42 PM, Peter Koukoulis 
> wrote:
>
>>
>> SQL> select dbms_sqlhash.gethash('select x,y from test1',2) as md5_value
>> from dual;
>>
>> MD5_VALUE
>> 
>> 
>> 9FDA7FA725B783172CA371DA04AD5754
>>
>>
>> Can I do something similar in PostgreSQL ?
>>
>>
> ​Similar.​
>
>  SELECT md5(string_agg(vals::text, ''))
>  FROM ( VALUES (1, 2), (2, 3) ) vals (x, y)
>
>
>
That is going to build up the entire string in memory, so will fail if the
text representation of the entire table doesn't fit in 1GB.

I don't see any feature in PostgreSQL for calculating hashes over streaming
data.  But it wouldn't be too hard to create something in plperl, for
example, to do that.  You would have to make sure the query always returns
rows in the same order (I don't know if Oracle's function handles that for
you) and that things like datestyle and timezone don't cause differences.

You could use something like:

\copy (select * from blah order by something) to program 'md5sum' binary

but I don't know how you would get the output back into your program once
it shows up on your screen.

Cheers,

Jeff

Re: [GENERAL] select md5 result set

[David G. Johnston]

On Wed, Aug 2, 2017 at 4:50 PM, Peter Koukoulis 
wrote:

> david, thanks for the help.
>
> Would this be the equivalent, for the statement in your email, for table
> TEST1 (x integer, y varchar(20)):
>
> ft_node=# SELECT md5(string_agg(vals::text, ''))
> ft_node-# from (select x,y from test1) vals(x,y);
> ?
>
>
​The subquery is redundant if you already have a table:

select md5(string_agg(test1::text, '')) from test1;

David J.​

Re: [GENERAL] select md5 result set

[Peter Koukoulis]

david, thanks for the help.

Would this be the equivalent, for the statement in your email, for table
TEST1 (x integer, y varchar(20)):

ft_node=# SELECT md5(string_agg(vals::text, ''))
ft_node-# from (select x,y from test1) vals(x,y);
?

Peter


On Thu, 3 Aug 2017 at 00:25 David G. Johnston 
wrote:

> On Wed, Aug 2, 2017 at 3:42 PM, Peter Koukoulis 
> wrote:
>
>>
>> SQL> select dbms_sqlhash.gethash('select x,y from test1',2) as md5_value
>> from dual;
>>
>> MD5_VALUE
>>
>> 
>> 9FDA7FA725B783172CA371DA04AD5754
>>
>>
>> Can I do something similar in PostgreSQL ?
>>
>>
> ​Similar.​
>
>  SELECT md5(string_agg(vals::text, ''))
>  FROM ( VALUES (1, 2), (2, 3) ) vals (x, y)
>
> ​David J.
>
>

Re: [GENERAL] select md5 result set

[David G. Johnston]

On Wed, Aug 2, 2017 at 3:42 PM, Peter Koukoulis 
wrote:

>
> SQL> select dbms_sqlhash.gethash('select x,y from test1',2) as md5_value
> from dual;
>
> MD5_VALUE
> 
> 
> 9FDA7FA725B783172CA371DA04AD5754
>
>
> Can I do something similar in PostgreSQL ?
>
>
​Similar.​

 SELECT md5(string_agg(vals::text, ''))
 FROM ( VALUES (1, 2), (2, 3) ) vals (x, y)

​David J.

[GENERAL] select md5 result set

[Peter Koukoulis]

Hi

I'm attempting to emulate feature available in Oracle, namely dbs_sqlhash.
For example, given the following table data values:

SQL> select x,y from test1;

X Y
-- 
5 White
1 YYY
2 Goodbye
6 Black

I can create a single hash value over the entire result set, specifically
md5, in a  query as follows:

SQL> select dbms_sqlhash.gethash('select x,y from test1',2) as md5_value
from dual;

MD5_VALUE

9FDA7FA725B783172CA371DA04AD5754


Can I do something similar in PostgreSQL ?

Thanks
P

Re: [GENERAL] SELECT statement with sub-queries

[Adrian Klaver]

On 05/28/2017 11:54 AM, Michelle Konzack wrote:

On 2017-05-28 11:23:47 Adrian Klaver hacked into the keyboard:

On 05/28/2017 10:53 AM, Michelle Konzack wrote:


SELECT * FROM products WHERE category IN
 (SELECT categories.cat FROM categories WHERE
 categories.serial = products.category);



Because you are comparing categories.cat


ehm no


Actually yes:

SELECT categories.cat FROM categories WHERE
categories.serial = products.category

is going to select categories.cat which is a varchar.

SELECT * FROM products WHERE category IN ...

is asking to select all fields from where the products.category field is 
in the output of the above sub-select, which reduces down to 
products.category = categories.cat

or
integer = varchar. As the error message says , that is not possible.



I want to replace in the output the numerical ID from "products.category"
with the value of "categories.cat", where the "products.category" match
the "categories.serial"



which is a varchar to
products.category which is an integer.  The above is crying out for
FOREIGN KEYS. For the time being I going to assume products.category
is a faux FK to categories.serial so;

SELECT * FROM products WHERE products.category = categories.serial;


My mistake, it should be:

SELECT categories.cat, manufacturer, p_name, p_images, p_desc
FROM products, categories WHERE products.category = categories.serial;



This is not working





--
Adrian Klaver
adrian.kla...@aklaver.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] SELECT statement with sub-queries

[David G. Johnston]

On Sun, May 28, 2017 at 11:54 AM, Michelle Konzack  wrote:

>
> I want to replace in the output the numerical ID from "products.category"
> with the value of "categories.cat", where the "products.category" match
> the "categories.serial"
>

​Then go and read the first response in this thread, ​Amitabh Kant's, and
confirm it works or say why it does not.  Personally I prefer "products
JOIN categories ON/USING" but that is style and the "FROM products,
categories WHERE" formulation will give the correct answer.

The syntax error in your original message is, like others have said,
because "==(int, int)" is not a known operator.

And typically one writes:  "EXISTS (correlated subquery)" instead of "IN
(correlated subquery)".  But if you want to replace a column in the output
a predicate subquery (WHERE clause) that effectively implements a semi-join
(only return results from one table) will not help you since you cannot
actually refer to any of the columns in the subquery in the main query.
You need an actual join to do that.  IOW, writing "FROM products WHERE"
when the output value you want is on the category table isn't going to help
you.

David J.

Re: [GENERAL] SELECT statement with sub-queries

[Michelle Konzack]

On 2017-05-28 20:19:59 m...@ft-c.de hacked into the keyboard:
> Hallo,
> 
> SELECT *
> FROM products
> WHERE exists
>  (SELECT categories.cat FROM categories WHERE
>  categories.serial==products.category);

This does not give an error but does nothing

> or
> SELECT * FROM products
> WHERE category IN
>   (SELECT categories.cat FROM categories);

This give an error

See previously mail for what I want to archive


-- 
Michelle KonzackMiila ITSystems @ TDnet
GNU/Linux Developer 00372-54541400


signature.asc
Description: Digital signature

Re: [GENERAL] SELECT statement with sub-queries

[Michelle Konzack]

On 2017-05-28 11:23:47 Adrian Klaver hacked into the keyboard:
> On 05/28/2017 10:53 AM, Michelle Konzack wrote:
> >>
> >>SELECT * FROM products WHERE category IN
> >> (SELECT categories.cat FROM categories WHERE
> >> categories.serial = products.category);

> Because you are comparing categories.cat

ehm no

I want to replace in the output the numerical ID from "products.category"
with the value of "categories.cat", where the "products.category" match
the "categories.serial"


> which is a varchar to
> products.category which is an integer.  The above is crying out for
> FOREIGN KEYS. For the time being I going to assume products.category
> is a faux FK to categories.serial so;
> 
> SELECT * FROM products WHERE products.category = categories.serial;

This is not working


-- 
Michelle KonzackMiila ITSystems @ TDnet
GNU/Linux Developer 00372-54541400


signature.asc
Description: Digital signature

Re: [GENERAL] SELECT statement with sub-queries

[ml]

On 28.05.2017 20:19, m...@ft-c.de wrote:

Hallo,

SELECT *
FROM products
WHERE exists
  (SELECT categories.cat FROM categories WHERE
  categories.serial==products.category);

   categories.serial = products.category);  -- one equal sign


or
SELECT * FROM products
WHERE category IN
   (SELECT categories.cat FROM categories);

On 28.05.2017 19:03, Michelle Konzack wrote:

Hello *,

I try to get columns from my database with a singel SELECT, but I stuck.

I have 3 tables like:


1)  categories (serial,cat)

2)  manufacturers (serial,m_name)

3)  products (serial,category,manufacturer,p_name)


where the "category" and "manufacturer"
are numerical IDs from the two tables above.

So I like to replace the numerical IDs with the appropriated values,
mean

SELECT * FROM products WHERE category IN
 (SELECT categories.cat FROM categories WHERE
 categories.serial==products.category);

But I get:

ERROR:  operator does not exist: integer==integer
LINE1: ...gories.cat FROM categories WHERE  
categories.serial==products.category);

  ^
Hmm, "serial" is an "integer" and the "category" and "manufacturer" too.

So whats wrong with it?













--
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] SELECT statement with sub-queries

[Adrian Klaver]

On 05/28/2017 10:53 AM, Michelle Konzack wrote:

On 2017-05-29 03:24:54 rob stone hacked into the keyboard:

You only need a single equals sign in SQL.

SELECT * FROM products WHERE category IN
 (SELECT categories.cat FROM categories WHERE
 categories.serial = products.category);


I have tried this too, but then I get:

ERROR:  operator does not exist: integer = character varying
LINE 1: SELECT * FROM products WHERE category IN (SELECT categories
   ^

My sql file is:

8<--
DROP TABLE categories;
DROP TABLE manufacturers;
DROP TABLE products;

CREATE TABLE categories (
serial  integer NOT NULL,
cat varchar(40),
);

CREATE TABLE manufacturers (
serial  integer NOT NULL,
m_name  varchar(40),
m_address   varchar(200),
m_imagesvarchar(100),
m_desc  varchar(1000),
);

CREATE TABLE products (
serial  integer NOT NULL,
categoryinteger NOT NULL,
manufacturerinteger NOT NULL,
p_name  varchar(40),
p_imagesvarchar(100),
p_desc  varchar(1),
);
8<--

This is WHY I am puzzeling arround with the "integer" error.


Because you are comparing categories.cat which is a varchar to 
products.category which is an integer.  The above is crying out for 
FOREIGN KEYS. For the time being I going to assume products.category is 
a faux FK to categories.serial so;


SELECT * FROM products WHERE products.category = categories.serial;








--
Adrian Klaver
adrian.kla...@aklaver.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] SELECT statement with sub-queries

[ml]

Hallo,

SELECT *
FROM products
WHERE exists
 (SELECT categories.cat FROM categories WHERE
 categories.serial==products.category);
or
SELECT * FROM products
WHERE category IN
  (SELECT categories.cat FROM categories);

On 28.05.2017 19:03, Michelle Konzack wrote:

Hello *,

I try to get columns from my database with a singel SELECT, but I stuck.

I have 3 tables like:


1)  categories (serial,cat)

2)  manufacturers (serial,m_name)

3)  products (serial,category,manufacturer,p_name)


where the "category" and "manufacturer"
are numerical IDs from the two tables above.

So I like to replace the numerical IDs with the appropriated values,
mean

SELECT * FROM products WHERE category IN
 (SELECT categories.cat FROM categories WHERE
 categories.serial==products.category);

But I get:

ERROR:  operator does not exist: integer==integer
LINE1: ...gories.cat FROM categories WHERE  
categories.serial==products.category);
  ^
Hmm, "serial" is an "integer" and the "category" and "manufacturer" too.

So whats wrong with it?










--
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] SELECT statement with sub-queries

[Michelle Konzack]

On 2017-05-29 03:24:54 rob stone hacked into the keyboard:
> You only need a single equals sign in SQL.
> 
> SELECT * FROM products WHERE category IN 
>     (SELECT categories.cat FROM categories WHERE 
>     categories.serial = products.category);

I have tried this too, but then I get:

ERROR:  operator does not exist: integer = character varying
LINE 1: SELECT * FROM products WHERE category IN (SELECT categories
  ^

My sql file is:

8<--
DROP TABLE categories;
DROP TABLE manufacturers;
DROP TABLE products;

CREATE TABLE categories (
serial  integer NOT NULL,
cat varchar(40),
);

CREATE TABLE manufacturers (
serial  integer NOT NULL,
m_name  varchar(40),
m_address   varchar(200),
m_imagesvarchar(100),
m_desc  varchar(1000),
);

CREATE TABLE products (
serial  integer NOT NULL,
categoryinteger NOT NULL,
manufacturerinteger NOT NULL,
p_name  varchar(40),
p_imagesvarchar(100),
p_desc  varchar(1),
);
8<--

This is WHY I am puzzeling arround with the "integer" error.


-- 
Michelle KonzackMiila ITSystems @ TDnet
GNU/Linux Developer 00372-54541400


signature.asc
Description: Digital signature

Re: [GENERAL] SELECT statement with sub-queries

[rob stone]

Hello,

On Sun, 2017-05-28 at 20:03 +0300, Michelle Konzack wrote:
> Hello *,
> 
> I try to get columns from my database with a singel SELECT, but I
> stuck.
> 
> I have 3 tables like:
> 
> 
> 1)  categories (serial,cat)
> 
> 2)  manufacturers (serial,m_name)
> 
> 3)  products (serial,category,manufacturer,p_name)
> 
> 
> where the "category" and "manufacturer"
> are numerical IDs from the two tables above.
> 
> So I like to replace the numerical IDs with the appropriated values,
> mean
> 
> SELECT * FROM products WHERE category IN 
> (SELECT categories.cat FROM categories WHERE 
> categories.serial==products.category);
> 
> But I get:
> 
> ERROR:  operator does not exist: integer==integer
> LINE1: ...gories.cat FROM categories
> WHERE  categories.serial==products.category);
>  ^
> Hmm, "serial" is an "integer" and the "category" and "manufacturer"
> too.
> 
> So whats wrong with it?
> 
> 

You only need a single equals sign in SQL.

SELECT * FROM products WHERE category IN 
    (SELECT categories.cat FROM categories WHERE 
    categories.serial = products.category);

Cheers,
Rob


-- 
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] SELECT statement with sub-queries

[Amitabh Kant]

On Sun, May 28, 2017 at 10:33 PM, Michelle Konzack  wrote:

> Hello *,
>
> I try to get columns from my database with a singel SELECT, but I stuck.
>
> I have 3 tables like:
>
>
> 1)  categories (serial,cat)
>
> 2)  manufacturers (serial,m_name)
>
> 3)  products (serial,category,manufacturer,p_name)
>
>
> where the "category" and "manufacturer"
> are numerical IDs from the two tables above.
>
> So I like to replace the numerical IDs with the appropriated values,
> mean
>
> SELECT * FROM products WHERE category IN
> (SELECT categories.cat FROM categories WHERE
> categories.serial==products.category);
>
> But I get:
>
> ERROR:  operator does not exist: integer==integer
> LINE1: ...gories.cat FROM categories WHERE  categories.serial==products.
> category);
>  ^
> Hmm, "serial" is an "integer" and the "category" and "manufacturer" too.
>
> So whats wrong with it?
>
>
>
>
>
>
>
> --
> Michelle KonzackMiila ITSystems @ TDnet
> GNU/Linux Developer 00372-54541400
>

Wouldn't a simple join like the one below suffice:

Select a.*,b.cat from products as a, categories as b where
a.category=b.serial

or am I missing something?

[GENERAL] SELECT statement with sub-queries

[Michelle Konzack]

Hello *,

I try to get columns from my database with a singel SELECT, but I stuck.

I have 3 tables like:


1)  categories (serial,cat)

2)  manufacturers (serial,m_name)

3)  products (serial,category,manufacturer,p_name)


where the "category" and "manufacturer"
are numerical IDs from the two tables above.

So I like to replace the numerical IDs with the appropriated values,
mean

SELECT * FROM products WHERE category IN 
(SELECT categories.cat FROM categories WHERE 
categories.serial==products.category);

But I get:

ERROR:  operator does not exist: integer==integer
LINE1: ...gories.cat FROM categories WHERE  
categories.serial==products.category);
 ^
Hmm, "serial" is an "integer" and the "category" and "manufacturer" too.

So whats wrong with it?







-- 
Michelle KonzackMiila ITSystems @ TDnet
GNU/Linux Developer 00372-54541400


signature.asc
Description: Digital signature

Re: [GENERAL] Select from tableA - if not exists then tableB

[Brian Dunavant]

On Tue, May 9, 2017 at 6:00 PM, Patrick B  wrote:
> SELECT
> split_part(n1.path::text, '/'::text, 18)::integer AS id,
> split_part(n1.path::text, '/'::text, 14)::integer AS clientid,
> lower(n1.md5::text)::character(32) AS md5, 0 AS cont,
> '-1000-1000-3000-6000'::uuid AS guid,
> n1.bytes AS byte_count,
> n1.last_modified AS last_modified
>   FROM tablea n1
>   JOIN tableb s2 ON s2.path = n1.path
>
> Where tablec is the new one. AS you can see, there is no reference for the
> new tablec on that query, so I need to:
>
> - Get the data from the new table,
> - if it is not in there, then go to old table (query above).


I'm assuming tablec is supposed to replace tablea.

Being a view makes it trickier.  You can still do it with:

SELECT
  split_part(n1.path::text, '/'::text, 18)::integer AS id,
  split_part(n1.path::text, '/'::text, 14)::integer AS clientid,
  lower(n1.md5::text)::character(32) AS md5, 0 AS cont,
  '-1000-1000-3000-6000'::uuid AS guid,
  n1.bytes AS byte_count,
  n1.last_modified AS last_modified
FROM (
 select DISTINCT ON (id) [columns] from (
select [columns/pads], 1 as tableorder from tablec
union all
select [columns/pads], 2 as tableorder from tablea
 ) t
 ORDER BY id, tableorder
   ) n1
  JOIN tableb s2 ON s2.path = n1.path;

This will cause it to prefer the data in tablec, but use any id's in
tablea that aren't in tablec .

This may be very slow, as i'm not sure if predicate pushdown would
happen here, so this may cause full table scans of both tablea and
tablec possibly making performance bad if those are large tables.   It
should do what you are asking for though.


-- 
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] Select from tableA - if not exists then tableB

[Patrick B]

2017-05-09 10:19 GMT+12:00 Brian Dunavant :

> From what you're saying about migrating, I'm assuming the new table
> has additional columns or something.  If you can map the difference,
> then you could use CTE's to select from the first table, and if
> nothing is there, then pull from the second table and pad it with
> nulls so they "match".  This should work fine in 9.1.
>
> For example:
>
> db=# create table old ( id integer );
> CREATE TABLE
> db=# create table new ( id integer, newcol text );
> CREATE TABLE
> db=# insert into old (id) values (1), (2);
> INSERT 0 2
> db=# insert into new (id, newcol) values (1, 'a');
> INSERT 0 1
>
> New table:
>
> db=# with new_check as (
> db(#   select id, newcol from new where id = 1
> db(# )
> db-# select id, null::text as newcol from old where id = 1
> db-# and not exists ( select 1 from new_check )
> db-# union all
> db-# select * from new_check;
>  id | newcol
> +
>   1 | a
> (1 row)
>
> Old table:
>
> db=# with new_check as (
> db(#   select id, newcol from new where id = 2
> db(# )
> db-# select id, null::text as newcol from old where id = 2
> db-# and not exists ( select 1 from new_check )
> db-# union all
> db-# select * from new_check;
>  id | newcol
> +
>   2 |
> (1 row)
>
> Neither:
>
> db=# with new_check as (
> db(#   select id, newcol from new where id = 3
> db(# )
> db-# select id, null::text as newcol from old where id = 3
> db-# and not exists ( select 1 from new_check )
> db-# union all
> db-# select * from new_check;
>  id | newcol
> +
> (0 rows)




Hmm.. that's interesting...

The query is (This is actually a view)

SELECT
split_part(n1.path::text, '/'::text, 18)::integer AS id,
split_part(n1.path::text, '/'::text, 14)::integer AS clientid,
lower(n1.md5::text)::character(32) AS md5, 0 AS cont,
'-1000-1000-3000-6000'::uuid AS guid,
n1.bytes AS byte_count,
n1.last_modified AS last_modified
  FROM tablea n1
  JOIN tableb s2 ON s2.path = n1.path

Where tablec is the new one. AS you can see, there is no reference for the
new tablec on that query, so I need to:

- Get the data from the new table,
- if it is not in there, then go to old table (query above).

\d tablec:

>
>  Table "public.tablec"
>Column   |Type |
>   Modifiers
>
> +-+---
>  id | integer | not null default
> nextval('tablec_id_seq'::regclass)
>  e_type| integer | not null
>  e_id  | bigint  |
>  e_variation   | character varying(16)   | not null
>  path  | character varying(255)  | not null
>  name  | character varying(255)  | not null
>  size  | bigint  | not null
>  md5   | md5_hash| not null
>  modified_date | timestamp without time zone | default
> statement_timestamp()
>  created_date  | timestamp without time zone | default
> statement_timestamp()
>  clientid | bigint  | not null
>  f_id| bigint  |



So, will the best way to use UNION ALL?

Thanks
Patrick.

Re: [GENERAL] Select from tableA - if not exists then tableB

[Brian Dunavant]

>From what you're saying about migrating, I'm assuming the new table
has additional columns or something.  If you can map the difference,
then you could use CTE's to select from the first table, and if
nothing is there, then pull from the second table and pad it with
nulls so they "match".  This should work fine in 9.1.

For example:

db=# create table old ( id integer );
CREATE TABLE
db=# create table new ( id integer, newcol text );
CREATE TABLE
db=# insert into old (id) values (1), (2);
INSERT 0 2
db=# insert into new (id, newcol) values (1, 'a');
INSERT 0 1

New table:

db=# with new_check as (
db(#   select id, newcol from new where id = 1
db(# )
db-# select id, null::text as newcol from old where id = 1
db-# and not exists ( select 1 from new_check )
db-# union all
db-# select * from new_check;
 id | newcol
+
  1 | a
(1 row)

Old table:

db=# with new_check as (
db(#   select id, newcol from new where id = 2
db(# )
db-# select id, null::text as newcol from old where id = 2
db-# and not exists ( select 1 from new_check )
db-# union all
db-# select * from new_check;
 id | newcol
+
  2 |
(1 row)

Neither:

db=# with new_check as (
db(#   select id, newcol from new where id = 3
db(# )
db-# select id, null::text as newcol from old where id = 3
db-# and not exists ( select 1 from new_check )
db-# union all
db-# select * from new_check;
 id | newcol
+
(0 rows)





On Mon, May 8, 2017 at 5:56 PM, Patrick B  wrote:
> Hi guys,
>
> I have two tables that supports the same data, but different table DDL (We
> are migrating all the data from one to another).
>
> What I need is basically:
>
> 1. Query looks for the data on table A,
> 2. if it doesn't find it on table A, go look for it on table B
>
> Now, how could I do that in a Select? Can you please provide some examples?
>
>
> I'm using PostgreSQL 9.1.
>
> Thanks
> Patrick


-- 
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] Select from tableA - if not exists then tableB

[David Rowley]

On 9 May 2017 at 09:56, Patrick B  wrote:
> Hi guys,
>
> I have two tables that supports the same data, but different table DDL (We
> are migrating all the data from one to another).
>
> What I need is basically:
>
> 1. Query looks for the data on table A,
> 2. if it doesn't find it on table A, go look for it on table B
>
> Now, how could I do that in a Select? Can you please provide some examples?
>
>
> I'm using PostgreSQL 9.1.

You could exploit DISTINCT ON for this.

Something like:

select distinct on (id) id,value from (select *,'a' tablename from a
where id=1 union all select *,'b' tablename from b where id=1) ab
order by id,tablename;

Assuming that id is what you want to be unique.


-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] Select from tableA - if not exists then tableB

[Adrian Klaver]

On 05/08/2017 02:56 PM, Patrick B wrote:

Hi guys,

I have two tables that supports the same data, but different table DDL
(We are migrating all the data from one to another).

What I need is basically:

1. Query looks for the data on table A,
2. if it doesn't find it on table A, go look for it on table B

Now, how could I do that in a Select? Can you please provide some examples?


If you can provide some information:

1) The table schema

2) How the data is related between the two tables.




I'm using PostgreSQL 9.1.

Thanks
Patrick



--
Adrian Klaver
adrian.kla...@aklaver.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] Select from tableA - if not exists then tableB

[Patrick B]

Hi guys,

I have two tables that supports the same data, but different table DDL (We
are migrating all the data from one to another).

What I need is basically:

1. Query looks for the data on table A,
2. if it doesn't find it on table A, go look for it on table B

Now, how could I do that in a Select? Can you please provide some examples?


I'm using PostgreSQL 9.1.

Thanks
Patrick

Re: [GENERAL] SELECT FOR UPDATE violates READ COMMITTED isolation?

[David G. Johnston]

On Wed, Apr 12, 2017 at 3:14 PM, Gavin Wahl  wrote:

> I think this paragraph explains why it happens:
> https://www.postgresql.org/docs/9.6/static/transaction-
> iso.html#XACT-READ-COMMITTED.
>
> > If the first updater commits, the second updater will ignore the row if
> the
> > first updater deleted it
>
> How is that allowed in READ COMMITTED? I never committed with 0 rows in
> test,
> so I expected to never have a SELECT that returns 0 rows.
>

​"... they will only find target rows that were committed as of the command
start time"​

The newly added test row from  did not exist when  began so it
can never been seen by .  Period.

"In this case, the would-be updater will wait for the first updating
transaction to commit or roll back (if it is still in progress)"

The  SELECT * FROM test FOR UPDATE; query sees every single row in
test but it indeed must wait due to the FOR UPDATE

"If the first updater commits, the second updater will ignore the row if
the first updater deleted it, otherwise it will attempt to apply its
operation to the updated version of the row."

Once  commits then  continues and ignores the now deleted rows.

Thus:

So, while there was never a time when the table contained zero rows all of
the rows that  wanted to see were gone by the time it got to them and
so it had nothing to show.  This is what is documented and what you show.

In short, this behavior is the price you pay for not having to concern
yourself with serialization errors - which is exactly what you would get if
you executed these transactions in any of the higher isolation levels.

David J.

[GENERAL] SELECT FOR UPDATE violates READ COMMITTED isolation?

[Gavin Wahl]

I have this table:

CREATE TABLE test (id INT PRIMARY KEY);
INSERT INTO test VALUES (1);

Then I run these two transactions simultaneously:

one| two
---+---
BEGIN; |
   | BEGIN;
DELETE FROM test; --DELETE 1   |
   | SELECT * FROM test FOR UPDATE; -- Blocks...
INSERT INTO test VALUES (1);   |
COMMIT;|
   | -- ...returns 0 rows

How is it possible that the select in transaction two returns 0 rows? There was
never a transaction that committed with 0 rows in test. Shouldn't read
committed isolation prevent this?

I think this paragraph explains why it happens:
https://www.postgresql.org/docs/9.6/static/transaction-iso.html#XACT-READ-COMMITTED.

> If the first updater commits, the second updater will ignore the row if the
> first updater deleted it

How is that allowed in READ COMMITTED? I never committed with 0 rows in test,
so I expected to never have a SELECT that returns 0 rows.


-- 
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] select on view shows different plan than select on table

[David G. Johnston]

On Wednesday, April 12, 2017, Bernd Lehmkuhl 
wrote:
>
> Why do I get different execution plans when querying the view like this:
> SELECT
>   *
> FROM
>   kkm_ergebnisse.v_protokoll_details_mit_dauer
> WHERE
>   id_rechenlauf = 123
> ORDER BY
>   schritt ;
>
> opposed to querying against the definition of the view?
> SELECT
>   id,
>   schritt,
>   objekt_typ,
>   objekt_id,
>   zeit,
>   rechenweg_thema,
>   rechenweg_variante,
>   rechenweg_stoffgruppe,
>   formel_inhalt,
>   formel_stoff,
>   formel_variablen,
>   ergebnis_variable,
>   ergebnis_wert,
>   status,
>   id_rechenlauf,
>   formel_id,
>   formel_name,
>   formel_variante,
>   ergebnis_variable_einheit,
>   zeitpunkt,
>   DATE_PART('seconds'::text, zeitpunkt::time without time zone -
> LAG(zeitpunkt::time without time zone) OVER (ORDER BY id)) * 1000::double
> precision AS dauer_in_ms
> FROM
>   kkm_ergebnisse.t_protokoll_details
> WHERE
>   id_rechenlauf = 123
> ORDER BY
>   schritt ;
>
>
The window aggregate defined in the view is an optimization fence which
prevents the view from having the where clause of the user pushed down.
Thus you are computing lag over all three million plus records in the table
before throwing away most of them.  When done inline the partition seen is
smaller and so is evaluated more quickly.

David J.

[GENERAL] select on view shows different plan than select on table

[Bernd Lehmkuhl]

Hi list,

I have a view defined as:
CREATE VIEW kkm_ergebnisse.v_protokoll_details_mit_dauer AS
SELECT
  id,
  schritt,
  objekt_typ,
  objekt_id,
  zeit,
  rechenweg_thema,
  rechenweg_variante,
  rechenweg_stoffgruppe,
  formel_inhalt,
  formel_stoff,
  formel_variablen,
  ergebnis_variable,
  ergebnis_wert,
  status,
  id_rechenlauf,
  formel_id,
  formel_name,
  formel_variante,
  ergebnis_variable_einheit,
  zeitpunkt,
  DATE_PART('seconds', zeitpunkt::time without time zone - 
LAG(zeitpunkt::time without time zone) OVER (ORDER BY id)) * 1000 AS 
dauer_in_ms

FROM
  kkm_ergebnisse.t_protokoll_details ;

Table kkm_ergebnisse.t_protokoll_details is defined like this:
CREATE TABLE kkm_ergebnisse.t_protokoll_details
(
  id serial NOT NULL,
  schritt integer,
  objekt_typ smallint NOT NULL,
  objekt_id integer,
  zeit integer,
  rechenweg_thema character varying(256),
  rechenweg_variante character varying(256),
  rechenweg_stoffgruppe character varying(256),
  formel_inhalt character varying(4000),
  formel_stoff character varying(256),
  formel_variablen character varying(4000),
  ergebnis_variable character varying(256),
  ergebnis_wert double precision,
  status character varying(4000),
  id_rechenlauf integer NOT NULL,
  formel_id integer,
  formel_name character varying(256),
  formel_variante character varying(256),
  ergebnis_variable_einheit character varying(255),
  zeitpunkt time with time zone DEFAULT clock_timestamp(),
  CONSTRAINT pk PRIMARY KEY (id),
  CONSTRAINT fk_rechenlauf FOREIGN KEY (id_rechenlauf)
  REFERENCES kkm_ergebnisse.t_rechenlaeufe (id) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT ck_protokoll_details_objekt_typ CHECK (objekt_typ = ANY 
(ARRAY[1, 2]))

);

Why do I get different execution plans when querying the view like this:
SELECT
  *
FROM
  kkm_ergebnisse.v_protokoll_details_mit_dauer
WHERE
  id_rechenlauf = 123
ORDER BY
  schritt ;

opposed to querying against the definition of the view?
SELECT
  id,
  schritt,
  objekt_typ,
  objekt_id,
  zeit,
  rechenweg_thema,
  rechenweg_variante,
  rechenweg_stoffgruppe,
  formel_inhalt,
  formel_stoff,
  formel_variablen,
  ergebnis_variable,
  ergebnis_wert,
  status,
  id_rechenlauf,
  formel_id,
  formel_name,
  formel_variante,
  ergebnis_variable_einheit,
  zeitpunkt,
  DATE_PART('seconds'::text, zeitpunkt::time without time zone - 
LAG(zeitpunkt::time without time zone) OVER (ORDER BY id)) * 
1000::double precision AS dauer_in_ms

FROM
  kkm_ergebnisse.t_protokoll_details
WHERE
  id_rechenlauf = 123
ORDER BY
  schritt ;

Execution plan query against view (slow):
Sort  (cost=570776.54..570779.14 rows=1037 width=347) (actual 
time=4067.919..4068.133 rows=11250 loops=1)
  Output: v_protokoll_details_mit_dauer.id, 
v_protokoll_details_mit_dauer.schritt, 
v_protokoll_details_mit_dauer.objekt_typ, 
v_protokoll_details_mit_dauer.objekt_id, 
v_protokoll_details_mit_dauer.zeit, 
v_protokoll_details_mit_dauer.rechenweg_thema, v_proto (...)

  Sort Key: v_protokoll_details_mit_dauer.schritt
  Sort Method: quicksort  Memory: 6185kB
  Buffers: shared hit=30925 read=166050
  ->  Subquery Scan on v_protokoll_details_mit_dauer 
(cost=0.43..570724.60 rows=1037 width=347) (actual 
time=4038.722..4062.481 rows=11250 loops=1)
Output: v_protokoll_details_mit_dauer.id, 
v_protokoll_details_mit_dauer.schritt, 
v_protokoll_details_mit_dauer.objekt_typ, 
v_protokoll_details_mit_dauer.objekt_id, 
v_protokoll_details_mit_dauer.zeit, 
v_protokoll_details_mit_dauer.rechenweg_thema, v (...)

Filter: (v_protokoll_details_mit_dauer.id_rechenlauf = 123)
Rows Removed by Filter: 3091203
Buffers: shared hit=30925 read=166050
->  WindowAgg  (cost=0.43..531778.35 rows=3115700 width=339) 
(actual time=0.127..3839.099 rows=3102453 loops=1)
  Output: pd.id, pd.schritt, pd.objekt_typ, pd.objekt_id, 
pd.zeit, pd.rechenweg_thema, pd.rechenweg_variante, 
pd.rechenweg_stoffgruppe, pd.formel_inhalt, pd.formel_stoff, 
pd.formel_variablen, pd.ergebnis_variable, pd.ergebnis_wert, pd.status, 
p (...)

  Buffers: shared hit=30925 read=166050
  ->  Index Scan using pk on 
kkm_ergebnisse.t_protokoll_details pd  (cost=0.43..446096.60 
rows=3115700 width=339) (actual time=0.109..1682.884 rows=3102453 loops=1)
Output: pd.id, pd.schritt, pd.objekt_typ, 
pd.objekt_id, pd.zeit, pd.rechenweg_thema, pd.rechenweg_variante, 
pd.rechenweg_stoffgruppe, pd.formel_inhalt, pd.formel_stoff, 
pd.formel_variablen, pd.ergebnis_variable, pd.ergebnis_wert, pd.sta (...)

Buffers: shared hit=30925 read=166050
Planning time: 0.323 ms
Execution time: 4069.073 ms

Execution plan of query against table (fast):
Sort  (cost=305.58..308.17 rows=1037 width=339) (actual 
time=51.558..52.140 rows=11250 loops=1)
  Output: id, schritt, objekt_typ, objekt_id, zeit, rechenweg_thema, 
rechenweg_variante, rechenweg_stoffgruppe, formel_inhalt, formel_stoff, 

Re: [GENERAL] SELECT and RowExclusiveLock

[Tom Lane]

Tim Nelson  writes:
> New to Postgres and I have never seen this condition.  We are getting test
> applications hanging on SELECT statements with a RowExclusiveLock.  How can
> a SELECT cause a RowExclusiveLock?

>  relname  |  pid  |   mode   | granted
> --+---+--+-
>  sales_transaction_detail |   392 | RowExclusiveLock | t
>  sales_transaction_detail | 19077 | RowExclusiveLock | t
>  sales_transaction_header | 32661 | RowExclusiveLock | t
>  sales_transaction_header |   392 | RowExclusiveLock | t
>  sales_transaction_header | 19077 | RowExclusiveLock | t

Hm, all those entries are showing granted = t, implying that they are
not blocked.  I think you are mis-querying pg_locks or mis-interpreting
the results.

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] SELECT and RowExclusiveLock

[David G. Johnston]

On Fri, Apr 7, 2017 at 1:25 PM, Tim Nelson  wrote:

> New to Postgres and I have never seen this condition.  We are getting test
> applications hanging on SELECT statements with a RowExclusiveLock.  How can
> a SELECT cause a RowExclusiveLock?
>

Two common ways:

SELECT ... FOR UPDATE;

SELECT function_that_performs_updates();

There are some more moving parts here, especially transactions, that may be
coming into play.  Its hard to say more given the limited and partial
detail.  Version and a more complete pg_stat_activity query would be
helpful.

David J.

Re: [GENERAL] SELECT and RowExclusiveLock

[Adrian Klaver]

On 04/07/2017 01:25 PM, Tim Nelson wrote:

New to Postgres and I have never seen this condition.  We are getting
test applications hanging on SELECT statements with a RowExclusiveLock.
How can a SELECT cause a RowExclusiveLock?

 relname  |  pid  |   mode   | granted
--+---+--+-
 sales_transaction_detail |   392 | RowExclusiveLock | t
 sales_transaction_detail | 19077 | RowExclusiveLock | t
 sales_transaction_header | 32661 | RowExclusiveLock | t
 sales_transaction_header |   392 | RowExclusiveLock | t
 sales_transaction_header | 19077 | RowExclusiveLock | t

  pid  |   age| usename  |
 query
---+--+--+--
 32661 | -07:42:39.289945 | postgres | UPDATE "sales_transaction_header"
SET "create_datetime" = '2017-04-07T02:20:39.4
 19077 | -07:42:15.976288 | postgres | SELECT "price_benefit"."id",
"price_benefit"."create_datetime", "price_benefit".
   392 | -07:01:44.121346 | postgres | SELECT "price_benefit"."id",
"price_benefit"."create_datetime", "price_benefit".


It would help to have:

1) Schema definitions for sales_transaction_detail and 
sales_transaction_header


2) The complete queries.

3) And just for grins the Postgres version.


--
Adrian Klaver
adrian.kla...@aklaver.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] SELECT and RowExclusiveLock

[Tim Nelson]

New to Postgres and I have never seen this condition.  We are getting test
applications hanging on SELECT statements with a RowExclusiveLock.  How can
a SELECT cause a RowExclusiveLock?

 relname  |  pid  |   mode   | granted
--+---+--+-
 sales_transaction_detail |   392 | RowExclusiveLock | t
 sales_transaction_detail | 19077 | RowExclusiveLock | t
 sales_transaction_header | 32661 | RowExclusiveLock | t
 sales_transaction_header |   392 | RowExclusiveLock | t
 sales_transaction_header | 19077 | RowExclusiveLock | t

  pid  |   age| usename  |
 query
---+--+--+--
 32661 | -07:42:39.289945 | postgres | UPDATE "sales_transaction_header"
SET "create_datetime" = '2017-04-07T02:20:39.4
 19077 | -07:42:15.976288 | postgres | SELECT "price_benefit"."id",
"price_benefit"."create_datetime", "price_benefit".
   392 | -07:01:44.121346 | postgres | SELECT "price_benefit"."id",
"price_benefit"."create_datetime", "price_benefit".

Re: [GENERAL] SELECT x'00000000F'::int leading zeros causes "integer out of range"

[Gavan Schneider]

On 2/25/17 at 6:56 AM, Gavin Flower wrote:


On 25/02/17 08:39, John McKown wrote:

On Fri, Feb 24, 2017 at 1:25 PM, David G. Johnston
On Friday, February 24, 2017, Tom Lane wrote:

Justin Pryzby writes:

Is this expected behavior ?
ts=# SELECT x'F'::int;
ERROR:  22003: integer out of range
LOCATION:  bittoint4, varbit.c:1575


Yes.  The provided operation is "convert a bitstring of up to
32 bits to an integer".  It's not "guess whether it's okay to
throw away some bits to make an integer".

IME The error message itself is to blame here - we are checking
for a malformed (too many characters) integer varbit
representation but then reporting that the we somehow got a valid
integer but that it is "out of range".

​A better reply would be good. Another possibility is for the parser
to remove unneeded leading zeros.​


[...]

I think the latter would be a good idea!

This is interesting in that the views expressed range from 
something close to "every bit is sacred" through to something 
resembling "drop what's needed to make it work".


My take is PostgreSQL is already pragmatic:

pendari=# select ((x'')::bigint)::int;
 int4
--
   -1
(1 row)

Clearly we've quietly dropped a lot of bits moving across this line.

The problem posed in the OP example happens when the bit pattern 
is under specifying a long value (or over specifying a short 
value), and, in an ideal world, the correct behaviour should be 
close to what all well behaved CPUs are already doing:


Opclass Operand Action (MSB=most significant bit)
==|===|==

logical/bitwise Small->LargeZero fill most significant, but
Large->Smallcheck which "standard" applies

arthmetic/signedSmall->LargePropagate sign bit to left
Large->SmallTruncate sign bits, error
if sign bits are not all equal,
and not equal to MSB of result

arithmetic/unsigSmall->LargeZero fill most significant part
Large->SmallTruncate from MSB, error if
any truncated bit is not zero

To my mind Tom's reply resembles the bitwise case but I think 
the OP's example should ideally have been interpreted in an 
arithmetic manner (i.e., treating the extra bits as representing 
the sign and nothing more) since the desired result was to be a 
signed integer.


But! This gets problematic for something like:  x'FFF67'::bigint

My analogy would have this interpreted as 
x'FF67'::bigint whereas the current behaviour is 
equivalent to x'000FFF67'::bigint, and I doubt anyone 
has the appetite to change this. (Of course we have always known 
using bit masks across architectures with different word sizes 
was never an easy or safe activity. :)


So, getting back to the OP problem… what's a good parser to do?

I suggest:
1. the error message might be better (i.e., help get the focus 
onto the real problem); and/or,
2. consider dropping excess leading zeros when building an 
integer value. (I don't think this breaks anything.)


Other than that there really isn't a realisable consistent 
behaviour beyond the current strict bitwise interpretation. 
Specifically any behaviour which tries to promote or truncate 
some "sign" bits in an arithmetically consistent manner is going 
to break existing behaviour.


Regards
Gavan Schneider



--
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] SELECT x'00000000F'::int leading zeros causes "integer out of range"

[Gavin Flower]

On 25/02/17 08:39, John McKown wrote:
On Fri, Feb 24, 2017 at 1:25 PM, David G. Johnston 
>wrote:


On Friday, February 24, 2017, Tom Lane > wrote:

Justin Pryzby  writes:
> Is this expected behavior ?
>   ts=# SELECT x'F'::int;
>   ERROR:  22003: integer out of range
>   LOCATION:  bittoint4, varbit.c:1575

Yes.  The provided operation is "convert a bitstring of up to
32 bits
to an integer".  It's not "guess whether it's okay to throw
away some
bits to make an integer".


IME The error message itself is to blame here - we are checking
for a malformed (too many characters) integer
varbit representation but then reporting that the we somehow got a
valid integer but that it is "out of range".


​A better reply would be good. Another possibility is for the parser 
to remove unneeded leading zeros.​



[...]

I think the latter would be a good idea!

Cheers,
Gavin


--
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] SELECT x'00000000F'::int leading zeros causes "integer out of range"

[John McKown]

On Fri, Feb 24, 2017 at 1:25 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Friday, February 24, 2017, Tom Lane  wrote:
>
>> Justin Pryzby  writes:
>> > Is this expected behavior ?
>> >   ts=# SELECT x'F'::int;
>> >   ERROR:  22003: integer out of range
>> >   LOCATION:  bittoint4, varbit.c:1575
>>
>> Yes.  The provided operation is "convert a bitstring of up to 32 bits
>> to an integer".  It's not "guess whether it's okay to throw away some
>> bits to make an integer".
>>
>>
> IME The error message itself is to blame here - we are checking for a
> malformed (too many characters) integer varbit representation but then
> reporting that the we somehow got a valid integer but that it is "out of
> range".
>

​A better reply would be good. Another possibility is for the parser to
remove unneeded leading zeros.​



>
> David J.
>



-- 
"Irrigation of the land with seawater desalinated by fusion power is
ancient. It's called 'rain'." -- Michael McClary, in alt.fusion

Maranatha! <><
John McKown

Re: [GENERAL] SELECT x'00000000F'::int leading zeros causes "integer out of range"

[David G. Johnston]

On Friday, February 24, 2017, Tom Lane  wrote:

> Justin Pryzby > writes:
> > Is this expected behavior ?
> >   ts=# SELECT x'F'::int;
> >   ERROR:  22003: integer out of range
> >   LOCATION:  bittoint4, varbit.c:1575
>
> Yes.  The provided operation is "convert a bitstring of up to 32 bits
> to an integer".  It's not "guess whether it's okay to throw away some
> bits to make an integer".
>
>
IME The error message itself is to blame here - we are checking for a
malformed (too many characters) integer varbit representation but then
reporting that the we somehow got a valid integer but that it is "out of
range".

David J.

Re: [GENERAL] SELECT x'00000000F'::int leading zeros causes "integer out of range"

[Melvin Davidson]

On Fri, Feb 24, 2017 at 2:13 PM, Tom Lane  wrote:

> Justin Pryzby  writes:
> > Is this expected behavior ?
> >   ts=# SELECT x'F'::int;
> >   ERROR:  22003: integer out of range
> >   LOCATION:  bittoint4, varbit.c:1575
>
> Yes.  The provided operation is "convert a bitstring of up to 32 bits
> to an integer".  It's not "guess whether it's okay to throw away some
> bits to make an integer".
>
> As an example, even if you think it's obvious that it would be okay
> to convert that bitstring to "15", would it be all right to convert
> x'F' to "-1" ?
>
> 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
>


In addition to what Tom said

SELECT x'F'::bigint;

works just fine.

IOW, int = 4 bytes, while bigint = 8 bytes.
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] SELECT x'00000000F'::int leading zeros causes "integer out of range"

[Tom Lane]

Justin Pryzby  writes:
> Is this expected behavior ?
>   ts=# SELECT x'F'::int;
>   ERROR:  22003: integer out of range
>   LOCATION:  bittoint4, varbit.c:1575

Yes.  The provided operation is "convert a bitstring of up to 32 bits
to an integer".  It's not "guess whether it's okay to throw away some
bits to make an integer".

As an example, even if you think it's obvious that it would be okay
to convert that bitstring to "15", would it be all right to convert
x'F' to "-1" ?

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] SELECT x'00000000F'::int leading zeros causes "integer out of range"

[Justin Pryzby]

Is this expected behavior ?

This works:

ts=# SELECT x'000F'::int;
int4|15

.. but an additional leading zero causes it to fail:

ts=# SELECT x'F'::int;
ERROR:  22003: integer out of range
LOCATION:  bittoint4, varbit.c:1575


|/* Check that the bit string is not too long */
|if (VARBITLEN(arg) > sizeof(result) * BITS_PER_BYTE)
|   ereport(ERROR,
|   (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
|errmsg("integer out of range")));

Justin


-- 
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] SELECT slow immediately after many update or delete+insert, except using WHERE .. IN

[Tom DalPozzo]

2016-12-10 15:41 GMT+01:00 Adrian Klaver :

> On 12/10/2016 04:21 AM, Tom DalPozzo wrote:
>
>> Hi,
>> my release is 9.5.4.
>> a took a look over it. I guessed that counting could be slow because it
>> needs to read everything and also that it can take advantage from an
>> index. But I don't understand why the delay is after the updates  for a
>>
>
> Best guess, autovacuum kicked in and marked a bunch of rows as no longer
> in play and thereby reduced the number of rows that needed to be counted.
>
> certain time and why WHERE..IN is much faster (ok, it's an index, but
>> I'm reading all the rows).
>>
>
> So per the second link have you tried something like:
>
> SELECT COUNT(*) FROM Table WHERE id > 0;
>
>
>> ​Hi,
no I ​

​did not (yet). But I guess that it would be similar to the one or to the
other. I will give updates if I try.
Regards
Pupillo

Re: [GENERAL] SELECT slow immediately after many update or delete+insert, except using WHERE .. IN

[Adrian Klaver]

On 12/10/2016 04:21 AM, Tom DalPozzo wrote:

Hi,
my release is 9.5.4.
a took a look over it. I guessed that counting could be slow because it
needs to read everything and also that it can take advantage from an
index. But I don't understand why the delay is after the updates  for a


Best guess, autovacuum kicked in and marked a bunch of rows as no longer 
in play and thereby reduced the number of rows that needed to be counted.



certain time and why WHERE..IN is much faster (ok, it's an index, but
I'm reading all the rows).


So per the second link have you tried something like:

SELECT COUNT(*) FROM Table WHERE id > 0;


Regards
Pupillo


2016-12-09 17:16 GMT+01:00 Adrian Klaver >:

On 12/09/2016 08:03 AM, Tom DalPozzo wrote:
> Hi,
> I did two tests:
> TEST 1
> 1 I created a table ("Table") with two fields, one ("Id") is a bigint
> and the other ("Data") is a bytea. Also created  an index on Id.
> 2 Populated the table with 1 rows, in which the bigint is
> incremental and bytea is 1000 bytes long.
> 3 Executed SELECT COUNT(*) FROM Table;.   It was very fast, almost
> immediate.
> 4 Updated 2000 of those rows for 1000 times. Each time using
BEGIN; 2000
> UPDATEs to bytea field (no length changed);COMMIT;   < It
> reached around 1 rows updated/sec.
> 5 Immediately after that, executed SELECT COUNT(*). It took nearly 2
> seconds.
> 6 After 1 minute,  executed SELECT COUNT(*). It was immediate again.
>
> TEST 2
> I dropped the table and redid the whole test1 from the beginning but
> using DELETE.. IN (...) + INSERT VALUES (...),(...),...;  instead of
> UPDATE  at point 4.
>  I noticed that:
> - Point 4 took half of the time used through UPDATE (hence now  2
> rows/sec)-
> - The slowness of SELECT COUNT(*)  remained much more than 1 min. (5
> mins?) After that it was fast again.
>
>
> BUT, in both tests, if I substitute point 5 with:
> SELECT * FROM Table WHERE Id IN (0,1,2,... all the numbers up to
);
> then it's almost immediate even if executed immediately after point 4
>
> 

What version of Postgres?

See:


https://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F



In particular:

https://wiki.postgresql.org/wiki/Slow_Counting


> Now the questions:
> I'd like to know the reason of the delay at point 5, in particular in
> the 2nd test and why it is faster when using WHERE..IN .
>
> Also, should I be concerned about the delay at point 5? I mean, my DB
> will receive around 20 millions of updates (or delete+insert) per day.
> Will this delay raise more and more along the months/years?
>
>
> Regards
> Pupillo
>
>
>
>
>
>
>
>


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





--
Adrian Klaver
adrian.kla...@aklaver.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] SELECT slow immediately after many update or delete+insert, except using WHERE .. IN

[Tom DalPozzo]

Hi,
my release is 9.5.4.
a took a look over it. I guessed that counting could be slow because it
needs to read everything and also that it can take advantage from an index.
But I don't understand why the delay is after the updates  for a certain
time and why WHERE..IN is much faster (ok, it's an index, but I'm reading
all the rows).
Regards
Pupillo


2016-12-09 17:16 GMT+01:00 Adrian Klaver :

> On 12/09/2016 08:03 AM, Tom DalPozzo wrote:
> > Hi,
> > I did two tests:
> > TEST 1
> > 1 I created a table ("Table") with two fields, one ("Id") is a bigint
> > and the other ("Data") is a bytea. Also created  an index on Id.
> > 2 Populated the table with 1 rows, in which the bigint is
> > incremental and bytea is 1000 bytes long.
> > 3 Executed SELECT COUNT(*) FROM Table;.   It was very fast, almost
> > immediate.
> > 4 Updated 2000 of those rows for 1000 times. Each time using BEGIN; 2000
> > UPDATEs to bytea field (no length changed);COMMIT;   < It
> > reached around 1 rows updated/sec.
> > 5 Immediately after that, executed SELECT COUNT(*). It took nearly 2
> > seconds.
> > 6 After 1 minute,  executed SELECT COUNT(*). It was immediate again.
> >
> > TEST 2
> > I dropped the table and redid the whole test1 from the beginning but
> > using DELETE.. IN (...) + INSERT VALUES (...),(...),...;  instead of
> > UPDATE  at point 4.
> >  I noticed that:
> > - Point 4 took half of the time used through UPDATE (hence now  2
> > rows/sec)-
> > - The slowness of SELECT COUNT(*)  remained much more than 1 min. (5
> > mins?) After that it was fast again.
> >
> >
> > BUT, in both tests, if I substitute point 5 with:
> > SELECT * FROM Table WHERE Id IN (0,1,2,... all the numbers up to );
> > then it's almost immediate even if executed immediately after point 4
> >
> > 
>
> What version of Postgres?
>
> See:
>
> https://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_
> count.28.2A.29_FROM_bigtable.3B.22_slow.3F
>
> In particular:
>
> https://wiki.postgresql.org/wiki/Slow_Counting
>
> > Now the questions:
> > I'd like to know the reason of the delay at point 5, in particular in
> > the 2nd test and why it is faster when using WHERE..IN .
> >
> > Also, should I be concerned about the delay at point 5? I mean, my DB
> > will receive around 20 millions of updates (or delete+insert) per day.
> > Will this delay raise more and more along the months/years?
> >
> >
> > Regards
> > Pupillo
> >
> >
> >
> >
> >
> >
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] SELECT slow immediately after many update or delete+insert, except using WHERE .. IN

[Adrian Klaver]

On 12/09/2016 08:03 AM, Tom DalPozzo wrote:
> Hi,
> I did two tests:
> TEST 1
> 1 I created a table ("Table") with two fields, one ("Id") is a bigint
> and the other ("Data") is a bytea. Also created  an index on Id.
> 2 Populated the table with 1 rows, in which the bigint is
> incremental and bytea is 1000 bytes long.
> 3 Executed SELECT COUNT(*) FROM Table;.   It was very fast, almost
> immediate.
> 4 Updated 2000 of those rows for 1000 times. Each time using BEGIN; 2000
> UPDATEs to bytea field (no length changed);COMMIT;   < It
> reached around 1 rows updated/sec.
> 5 Immediately after that, executed SELECT COUNT(*). It took nearly 2
> seconds.
> 6 After 1 minute,  executed SELECT COUNT(*). It was immediate again.
> 
> TEST 2
> I dropped the table and redid the whole test1 from the beginning but
> using DELETE.. IN (...) + INSERT VALUES (...),(...),...;  instead of
> UPDATE  at point 4.
>  I noticed that:
> - Point 4 took half of the time used through UPDATE (hence now  2
> rows/sec)-
> - The slowness of SELECT COUNT(*)  remained much more than 1 min. (5
> mins?) After that it was fast again.
> 
> 
> BUT, in both tests, if I substitute point 5 with: 
> SELECT * FROM Table WHERE Id IN (0,1,2,... all the numbers up to );
> then it's almost immediate even if executed immediately after point 4
> 
> 

What version of Postgres?

See:

https://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F

In particular:

https://wiki.postgresql.org/wiki/Slow_Counting

> Now the questions:
> I'd like to know the reason of the delay at point 5, in particular in
> the 2nd test and why it is faster when using WHERE..IN . 
> 
> Also, should I be concerned about the delay at point 5? I mean, my DB
> will receive around 20 millions of updates (or delete+insert) per day.
> Will this delay raise more and more along the months/years? 
> 
> 
> Regards
> Pupillo
> 
> 
> 
> 
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.kla...@aklaver.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] SELECT slow immediately after many update or delete+insert, except using WHERE .. IN

[Tom DalPozzo]

Hi,
I did two tests:
TEST 1
1 I created a table ("Table") with two fields, one ("Id") is a bigint and
the other ("Data") is a bytea. Also created  an index on Id.
2 Populated the table with 1 rows, in which the bigint is incremental
and bytea is 1000 bytes long.
3 Executed SELECT COUNT(*) FROM Table;.   It was very fast, almost
immediate.
4 Updated 2000 of those rows for 1000 times. Each time using BEGIN; 2000
UPDATEs to bytea field (no length changed);COMMIT;   < It
reached around 1 rows updated/sec.
5 Immediately after that, executed SELECT COUNT(*). It took nearly 2
seconds.
6 After 1 minute,  executed SELECT COUNT(*). It was immediate again.

TEST 2
I dropped the table and redid the whole test1 from the beginning but using
DELETE.. IN (...) + INSERT VALUES (...),(...),...;  instead of UPDATE  at
point 4.
 I noticed that:
- Point 4 took half of the time used through UPDATE (hence now  2
rows/sec)-
- The slowness of SELECT COUNT(*)  remained much more than 1 min. (5 mins?)
After that it was fast again.


BUT, in both tests, if I substitute point 5 with:
SELECT * FROM Table WHERE Id IN (0,1,2,... all the numbers up to );
then it's almost immediate even if executed immediately after point 4


Now the questions:
I'd like to know the reason of the delay at point 5, in particular in the
2nd test and why it is faster when using WHERE..IN .

Also, should I be concerned about the delay at point 5? I mean, my DB will
receive around 20 millions of updates (or delete+insert) per day. Will this
delay raise more and more along the months/years?


Regards
Pupillo

Re: [HACKERS] [GENERAL] Select works only when connected from login postgres

[Tom Lane]

Joseph Brenner  writes:
> I thought I'd reproduced the behavior in an xterm, but I was just
> trying again and I don't see it.  It does seem that the dumbness of my
> dumb terminal is a factor.

Evidently.

> If I understand the way this works, it could be an even more baffling
> behavior if I were using an xterm: with a blank PAGER your output
> would disappear only if the select exceeded a certain number of
> lines...

Yeah, that was exactly the behavior I was seeing before fixing it
(the fix is pushed btw).

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: [HACKERS] [GENERAL] Select works only when connected from login postgres

[Joseph Brenner]

Yes, I have a tendency to use emacs sub-shells (and occasionally M-x
sql-postgres)--

I thought I'd reproduced the behavior in an xterm, but I was just
trying again and I don't see it.  It does seem that the dumbness of my
dumb terminal is a factor.

If I understand the way this works, it could be an even more baffling
behavior if I were using an xterm: with a blank PAGER your output
would disappear only if the select exceeded a certain number of
lines...


On Wed, Dec 7, 2016 at 2:31 AM, Daniel Verite  wrote:
> Tom Lane wrote:
>
>> BTW, I realized while testing this that there's still one gap in our
>> understanding of what went wrong for you: cases like "SELECT 'hello'"
>> should not have tried to use the pager, because that would've produced
>> less than a screenful of data
>
> At some point emacs was mentioned as the terminal:
>
>>> And I guess I did that intentionally, my .bashrc has
>>>
>>>   # I use emacs shells, I got a "pager" already:
>>>   export PAGER=''
>
> The M-x shell mode of emacs has a so-called "dumb" terminal
> emulation (that's the value of $TERM) where the notion of a "page"
> doesn't quite apply.
>
> For instance, when using emacs 24.3 with my default pager on an
> Ubuntu desktop, this is what I get:
>
> test=> select 1;
> WARNING: terminal is not fully functional
> -  (press RETURN)
>  ?column?
> --
> 1
> (1 row)
>
> I suspect that psql is unable to determine the screen size
> of the "dumb" terminal, and that it's the fault of the terminal
> rather than psql.
> The warning is displayed by "less" AFAICS.
>
> There are other psql features like tab-completion that don't work
> in this mode because emacs interpret keystrokes first for
> itself, in effect mixing emacs functionalities with these of the
> application run in the terminal. It's awesome sometimes
> and irritating at other times depending on what you expect :)
>
> OTOH it has also a M-x term command/mode that provides a
> more sophisticated screen emulation into which paging seems
> to work exactly like in a normal terminal and the emacs key bindings
> are turned off.
>
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite


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

Re: [HACKERS] [GENERAL] Select works only when connected from login postgres

[Daniel Verite]

Tom Lane wrote:

> BTW, I realized while testing this that there's still one gap in our
> understanding of what went wrong for you: cases like "SELECT 'hello'"
> should not have tried to use the pager, because that would've produced
> less than a screenful of data

At some point emacs was mentioned as the terminal:

>> And I guess I did that intentionally, my .bashrc has
>>
>>   # I use emacs shells, I got a "pager" already:
>>   export PAGER=''

The M-x shell mode of emacs has a so-called "dumb" terminal
emulation (that's the value of $TERM) where the notion of a "page"
doesn't quite apply.

For instance, when using emacs 24.3 with my default pager on an
Ubuntu desktop, this is what I get:

test=> select 1;
WARNING: terminal is not fully functional
-  (press RETURN)
 ?column? 
--
1
(1 row)

I suspect that psql is unable to determine the screen size
of the "dumb" terminal, and that it's the fault of the terminal
rather than psql.
The warning is displayed by "less" AFAICS.

There are other psql features like tab-completion that don't work
in this mode because emacs interpret keystrokes first for
itself, in effect mixing emacs functionalities with these of the
application run in the terminal. It's awesome sometimes
and irritating at other times depending on what you expect :)

OTOH it has also a M-x term command/mode that provides a
more sophisticated screen emulation into which paging seems
to work exactly like in a normal terminal and the emacs key bindings
are turned off. 


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite


-- 
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] Select works only when connected from login postgres

[Tom Lane]

Joseph Brenner  writes:
> Well, my take would be that if you've taken the trouble to set an
> empty string as the PAGER that means something, and it probably means
> you don't want any pager to be used.

Yeah, on reflection that argument seems pretty persuasive.  So I propose
the attached patch.

BTW, I realized while testing this that there's still one gap in our
understanding of what went wrong for you: cases like "SELECT 'hello'"
should not have tried to use the pager, because that would've produced
less than a screenful of data.  But that's irrelevant here, because it
can easily be shown that psql doesn't behave nicely if PAGER is set to
empty when it does try to use the pager.

regards, tom lane

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 261652a..9915731 100644
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*** $endif
*** 3801,3808 
If the query results do not fit on the screen, they are piped
through this command.  Typical values are
more or less.  The default
!   is platform-dependent.  The use of the pager can be disabled by
!   using the \pset command.
   
  
 
--- 3801,3809 
If the query results do not fit on the screen, they are piped
through this command.  Typical values are
more or less.  The default
!   is platform-dependent.  Use of the pager can be disabled by setting
!   PAGER to empty, or by using pager-related options of
!   the \pset command.
   
  
 
diff --git a/src/fe_utils/print.c b/src/fe_utils/print.c
index 1ec74f1..5c5d285 100644
*** a/src/fe_utils/print.c
--- b/src/fe_utils/print.c
*** PageOutput(int lines, const printTableOp
*** 2874,2879 
--- 2874,2885 
  			pagerprog = getenv("PAGER");
  			if (!pagerprog)
  pagerprog = DEFAULT_PAGER;
+ 			else
+ 			{
+ /* if PAGER is empty or all-white-space, don't use pager */
+ if (strspn(pagerprog, " \t\r\n") == strlen(pagerprog))
+ 	return stdout;
+ 			}
  			disable_sigpipe_trap();
  			pagerpipe = popen(pagerprog, "w");
  			if (pagerpipe)
diff --git a/src/interfaces/libpq/fe-print.c b/src/interfaces/libpq/fe-print.c
index c33dc42..e596a51 100644
*** a/src/interfaces/libpq/fe-print.c
--- b/src/interfaces/libpq/fe-print.c
*** PQprint(FILE *fout, const PGresult *res,
*** 166,173 
  			screen_size.ws_col = 80;
  #endif
  			pagerenv = getenv("PAGER");
  			if (pagerenv != NULL &&
! pagerenv[0] != '\0' &&
  !po->html3 &&
  ((po->expanded &&
    nTups * (nFields + 1) >= screen_size.ws_row) ||
--- 166,174 
  			screen_size.ws_col = 80;
  #endif
  			pagerenv = getenv("PAGER");
+ 			/* if PAGER is unset, empty or all-white-space, don't use pager */
  			if (pagerenv != NULL &&
! strspn(pagerenv, " \t\r\n") != strlen(pagerenv) &&
  !po->html3 &&
  ((po->expanded &&
    nTups * (nFields + 1) >= screen_size.ws_row) ||

-- 
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] Select works only when connected from login postgres

[Joseph Brenner]

But on the other hand, if you've got a blank PAGER envar and a "\pset
pager something", the pset should win (just as it does now).


On Tue, Dec 6, 2016 at 1:53 PM, Joseph Brenner  wrote:
> Well, my take would be that if you've taken the trouble to set an
> empty string as the PAGER that means something, and it probably means
> you don't want any pager to be used.
>
> But then, I would say that.
>
>
> On Tue, Dec 6, 2016 at 12:13 PM, Tom Lane  wrote:
>> Joseph Brenner  writes:
 Agreed.  One thing that would be very simple is to treat an empty PAGER
 value the same as "unset".
>>
>>> Sounds excellent.
>>
>> Actually, after thinking about it a bit longer, should PAGER-set-but-
>> empty be treated as equivalent to "pager off", rather than as a request
>> to use the default pager?  I could see arguments either way for that.
>>
 Detecting whether a nonempty value is behaving
 sanely seems a great deal harder ...
>>
>>> I was thinking a check for existence and executability, but I guess
>>> that's covered already...  if you use a random string as PAGER you get
>>> a sh error:
>>
>>>   export PAGER="nadatech"
>>>   /usr/lib/postgresql/9.6/bin/psql --no-psqlrc --dbname=doom
>>> --username=doom -p 5434 --host=/var/run/postgresql --command="SELECT
>>> 'hello' AS world;"
>>
>>>   sh: 1: nadatech: not found
>>
>> Hm, so you do; so my thought that this needs explicit code on our end
>> seems wrong.  [ experiments... ]  It seems like the specific case of
>> PAGER being empty or all-white-space causes the shell to think that
>> it's executing an empty line and just do nothing (in particular, not
>> print any error).  pclose then returns EPIPE, at least on my platform,
>> which we could report but it doesn't seem like a very useful report.
>> Any other case seems to provoke a shell complaint that's probably
>> sufficient for diagnosis.
>>
>> So what I'm thinking now is that if PAGER is empty or all white space
>> then we should not try to use it as a shell command; we can either
>> treat the case as "pager off" or as "use default pager".  Everything
>> else we can leave to the invoked shell to complain about.
>>
>> Comments?
>>
>> 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] Select works only when connected from login postgres

[Joseph Brenner]

Well, my take would be that if you've taken the trouble to set an
empty string as the PAGER that means something, and it probably means
you don't want any pager to be used.

But then, I would say that.


On Tue, Dec 6, 2016 at 12:13 PM, Tom Lane  wrote:
> Joseph Brenner  writes:
>>> Agreed.  One thing that would be very simple is to treat an empty PAGER
>>> value the same as "unset".
>
>> Sounds excellent.
>
> Actually, after thinking about it a bit longer, should PAGER-set-but-
> empty be treated as equivalent to "pager off", rather than as a request
> to use the default pager?  I could see arguments either way for that.
>
>>> Detecting whether a nonempty value is behaving
>>> sanely seems a great deal harder ...
>
>> I was thinking a check for existence and executability, but I guess
>> that's covered already...  if you use a random string as PAGER you get
>> a sh error:
>
>>   export PAGER="nadatech"
>>   /usr/lib/postgresql/9.6/bin/psql --no-psqlrc --dbname=doom
>> --username=doom -p 5434 --host=/var/run/postgresql --command="SELECT
>> 'hello' AS world;"
>
>>   sh: 1: nadatech: not found
>
> Hm, so you do; so my thought that this needs explicit code on our end
> seems wrong.  [ experiments... ]  It seems like the specific case of
> PAGER being empty or all-white-space causes the shell to think that
> it's executing an empty line and just do nothing (in particular, not
> print any error).  pclose then returns EPIPE, at least on my platform,
> which we could report but it doesn't seem like a very useful report.
> Any other case seems to provoke a shell complaint that's probably
> sufficient for diagnosis.
>
> So what I'm thinking now is that if PAGER is empty or all white space
> then we should not try to use it as a shell command; we can either
> treat the case as "pager off" or as "use default pager".  Everything
> else we can leave to the invoked shell to complain about.
>
> Comments?
>
> 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] Select works only when connected from login postgres

[David G. Johnston]

On Tue, Dec 6, 2016 at 1:13 PM, Tom Lane  wrote:

> So what I'm thinking now is that if PAGER is empty or all white space
> then we should not try to use it as a shell command; we can either
> treat the case as "pager off" or as "use default pager".  Everything
> else we can leave to the invoked shell to complain about.
>

​My pick would be to leave \pset pager to control the on/off nature of the
pager and treat both an unset and an empty string PAGER identically and
solely for the purpose of choosing which pager to use should one be
required.

David J.
​

Re: [GENERAL] Select works only when connected from login postgres

[Tom Lane]

Joseph Brenner  writes:
>> Agreed.  One thing that would be very simple is to treat an empty PAGER
>> value the same as "unset".

> Sounds excellent.

Actually, after thinking about it a bit longer, should PAGER-set-but-
empty be treated as equivalent to "pager off", rather than as a request
to use the default pager?  I could see arguments either way for that.

>> Detecting whether a nonempty value is behaving
>> sanely seems a great deal harder ...

> I was thinking a check for existence and executability, but I guess
> that's covered already...  if you use a random string as PAGER you get
> a sh error:

>   export PAGER="nadatech"
>   /usr/lib/postgresql/9.6/bin/psql --no-psqlrc --dbname=doom
> --username=doom -p 5434 --host=/var/run/postgresql --command="SELECT
> 'hello' AS world;"

>   sh: 1: nadatech: not found

Hm, so you do; so my thought that this needs explicit code on our end
seems wrong.  [ experiments... ]  It seems like the specific case of
PAGER being empty or all-white-space causes the shell to think that
it's executing an empty line and just do nothing (in particular, not
print any error).  pclose then returns EPIPE, at least on my platform,
which we could report but it doesn't seem like a very useful report.
Any other case seems to provoke a shell complaint that's probably
sufficient for diagnosis.

So what I'm thinking now is that if PAGER is empty or all white space
then we should not try to use it as a shell command; we can either
treat the case as "pager off" or as "use default pager".  Everything
else we can leave to the invoked shell to complain about.

Comments?

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] Select works only when connected from login postgres

[Joseph Brenner]

> Agreed.  One thing that would be very simple is to treat an empty PAGER
> value the same as "unset".

Sounds excellent.

> Detecting whether a nonempty value is behaving
> sanely seems a great deal harder ...

I was thinking a check for existence and executability, but I guess
that's covered already...  if you use a random string as PAGER you get
a sh error:

  export PAGER="nadatech"
  /usr/lib/postgresql/9.6/bin/psql --no-psqlrc --dbname=doom
--username=doom -p 5434 --host=/var/run/postgresql --command="SELECT
'hello' AS world;"

  sh: 1: nadatech: not found

So the empty PAGER value case is the only one that doesn't seem
covered already.  (I'm talented about finding these things...)






On Tue, Dec 6, 2016 at 9:51 AM, Tom Lane  wrote:
> Joseph Brenner  writes:
>> Looking back on the order of events, I think it went like this:
>> [ careful postmortem ]
>
> Thanks for following up!
>
>> So yeah, some better messaging when PAGER is mangled wouldn't hurt, if
>> that's possible.  Falling back to "pager off" would make sense to me.
>
> Agreed.  One thing that would be very simple is to treat an empty PAGER
> value the same as "unset".  Detecting whether a nonempty value is behaving
> sanely seems a great deal harder; depending on what pager you're using
> and how you stop it, nonzero exit codes from the called process might
> be normal.  I think it might be practical to issue a warning if we get
> an exit code of 126 or 127, though.  We have a comment in archive-command
> invocation:
>
>  * Per the Single Unix Spec, shells report exit status > 128 when a called
>  * command died on a signal.  Also, 126 and 127 are used to report
>  * problems such as an unfindable command; treat those as fatal errors
>  * too.
>
> The relevant text in POSIX is
>
> If a command is not found, the exit status shall be 127. If the
> command name is found, but it is not an executable utility, the
> exit status shall be 126. Applications that invoke utilities
> without using the shell should use these exit status values to
> report similar errors.
>
> I don't believe we want to complain about exit on a signal, because
> SIGTERM or SIGINT is a typical exit in some pager setups.  But these
> two codes strongly suggest something broken about your PAGER value.
>
> So I propose
> (1) ignore PAGER if it's an empty string
> (2) if pclose returns exit code 126 or 127, report that the PAGER
> command didn't work.  I'm not sure how complex that is, because IIRC
> the pclose is at some remove from the popen call, but if it's not
> unreasonably hairy we should do it.
>
> 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] Select works only when connected from login postgres

[Tom Lane]

Joseph Brenner  writes:
> Looking back on the order of events, I think it went like this:
> [ careful postmortem ]

Thanks for following up!

> So yeah, some better messaging when PAGER is mangled wouldn't hurt, if
> that's possible.  Falling back to "pager off" would make sense to me.

Agreed.  One thing that would be very simple is to treat an empty PAGER
value the same as "unset".  Detecting whether a nonempty value is behaving
sanely seems a great deal harder; depending on what pager you're using
and how you stop it, nonzero exit codes from the called process might
be normal.  I think it might be practical to issue a warning if we get
an exit code of 126 or 127, though.  We have a comment in archive-command
invocation:

 * Per the Single Unix Spec, shells report exit status > 128 when a called
 * command died on a signal.  Also, 126 and 127 are used to report
 * problems such as an unfindable command; treat those as fatal errors
 * too.

The relevant text in POSIX is

If a command is not found, the exit status shall be 127. If the
command name is found, but it is not an executable utility, the
exit status shall be 126. Applications that invoke utilities
without using the shell should use these exit status values to
report similar errors.

I don't believe we want to complain about exit on a signal, because
SIGTERM or SIGINT is a typical exit in some pager setups.  But these
two codes strongly suggest something broken about your PAGER value.

So I propose
(1) ignore PAGER if it's an empty string
(2) if pclose returns exit code 126 or 127, report that the PAGER
command didn't work.  I'm not sure how complex that is, because IIRC
the pclose is at some remove from the popen call, but if it's not
unreasonably hairy we should do it.

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] Select works only when connected from login postgres

[Joseph Brenner]

Looking back on the order of events, I think it went like this:

Back in around May, I was annoyed at pager behavior and wanted to get
rid of them-- I tried a blank PAGER setting in my .bashrc (and forgot
about it).

I also noticed the psql option "\pset pager off".  For not particular
reason, it didn't occur to me to just put that in a .psqlrc file, and
instead I had it in a command-line alias:

  alias psql="psql --pset='pager=off'"

Then more recently I started experimenting with a new build of 9.6.1:

The behavior at that stage looked like a difference between 9.4 (run
with a bare "psql") and 9.6 (run using a path to the new build).

Then later, as I was experimenting with multiple postgres
installations, I  got more careful about using paths to make sure I
knew which one I was running.  That cut the alias out of the picture,
and the .bashrc setting took over:

The behavior at that stage looked like a difference between user login
'doom' and 'postgres'.

I kept chasing after obscure file permissions settings or postgres
internal permissions (maybe there was something about the new row
level security features?), and so on.  The folks here kept assuming it
had to be some kind of version skew cross-talk between the different
installations.

Something I'd thought of vaguely (and wish I'd tried) was just
creating a new user and trying to reproduce the behavior in a clean
account set-up (there are always options to suppress rc files for
testing, but suppressing .bashrc isn't that simple).

The next thing on the list for me was to remove everything except the
new 9.6.1 pgdb binary install: by itself that wouldn't have gotten any
closer to isolating the problem, but it would've eliminated some
distractions.

Oddly enough, if I'd been reading up-to-date pg docs, I might not have
tried the .psqlrc setup that pointed the finger at the pager
situation: that was just something I was doing on the side, and I
didn't think it'd have any effect on a --command invocation, so I
didn't worry about it.

So yeah, some better messaging when PAGER is mangled wouldn't hurt, if
that's possible.  Falling back to "pager off" would make sense to me.











On Mon, Dec 5, 2016 at 9:28 PM, Joseph Brenner  wrote:
> Well yeah, trying to run a PAGER that's not there might throw an error.
> Or you know, nothing in PAGER might imply "pager off".
>
>> I find it a bit odd that all of your queries were using the pager...did I 
>> miss where you reported that setting?
>
> I didn't report it because I wasn't looking in that direction.   A
> PAGER set to blank for login 'doom' and no PAGER setting for login
> 'postgres' explains much of what I was seeing, I think: selects run as
>  'doom' tended to be blank (unless I had a pset no pager somewhere),
> selects run as 'postgres' always worked.
>
>
>
>
>
>
> On Mon, Dec 5, 2016 at 9:03 PM, David G. Johnston
>  wrote:
>> On Mon, Dec 5, 2016 at 9:53 PM, Joseph Brenner  wrote:
>>>
>>> And I guess I did that intentionally, my .bashrc has
>>>
>>>   # I use emacs shells, I got a "pager" already:
>>>   export PAGER=''
>>>
>>
>> PAGER= psql --pset=pager=always -c 'select 1;'
>> 
>>
>> Remove PAGER= and I'm good.
>>
>> I guess that psql could be a bit more helpful by reporting something to
>> stderr if the value of PAGER is not an executable (platform dependent...)
>>
>> I find it a bit odd that all of your queries were using the pager...did I
>> miss where you reported that setting?
>>
>> David J.
>>


-- 
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] Select works only when connected from login postgres

[Joseph Brenner]

Well yeah, trying to run a PAGER that's not there might throw an error.
Or you know, nothing in PAGER might imply "pager off".

> I find it a bit odd that all of your queries were using the pager...did I 
> miss where you reported that setting?

I didn't report it because I wasn't looking in that direction.   A
PAGER set to blank for login 'doom' and no PAGER setting for login
'postgres' explains much of what I was seeing, I think: selects run as
 'doom' tended to be blank (unless I had a pset no pager somewhere),
selects run as 'postgres' always worked.






On Mon, Dec 5, 2016 at 9:03 PM, David G. Johnston
 wrote:
> On Mon, Dec 5, 2016 at 9:53 PM, Joseph Brenner  wrote:
>>
>> And I guess I did that intentionally, my .bashrc has
>>
>>   # I use emacs shells, I got a "pager" already:
>>   export PAGER=''
>>
>
> PAGER= psql --pset=pager=always -c 'select 1;'
> 
>
> Remove PAGER= and I'm good.
>
> I guess that psql could be a bit more helpful by reporting something to
> stderr if the value of PAGER is not an executable (platform dependent...)
>
> I find it a bit odd that all of your queries were using the pager...did I
> miss where you reported that setting?
>
> David J.
>


-- 
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] Select works only when connected from login postgres

[David G. Johnston]

On Mon, Dec 5, 2016 at 9:53 PM, Joseph Brenner  wrote:

> And I guess I did that intentionally, my .bashrc has
>
>   # I use emacs shells, I got a "pager" already:
>   export PAGER=''
>
>
​PAGER= psql --pset=pager=always -c 'select 1;'​


Remove PAGER= and I'm good.

I guess that psql could be a bit more helpful by reporting something to
stderr if the value of PAGER is not an executable (platform dependent...)

I find it a bit odd that all of your queries were using the pager...did I
miss where you reported that setting?

David J.

Re: [GENERAL] Select works only when connected from login postgres

[Joseph Brenner]

And I guess I did that intentionally, my .bashrc has

  # I use emacs shells, I got a "pager" already:
  export PAGER=''

On Mon, Dec 5, 2016 at 8:52 PM, Joseph Brenner  wrote:
> Wait, that's not quite right.  The user 'postgres' has no PAGER envar,
> but user 'doom' has an empty value:
>
> PAGER=
>
>
> On Mon, Dec 5, 2016 at 8:50 PM, Joseph Brenner  wrote:
>>> So what does:
>>>
>>> env | grep PAGER
>>>
>>> show?
>>
>> Nothing.  I have no PAGER settting (I don't normally use one).
>>
>>
>> On Mon, Dec 5, 2016 at 5:33 PM, Adrian Klaver  
>> wrote:
>>> On 12/05/2016 05:13 PM, Joseph Brenner wrote:
>>>
>>>
 I just went around temporarily undoing things I did while
 straigtening up, and I find there's one thing I can do that
 consistently breaks things: removing my new ~/.psqlrc file.
 In fact, it appears that I need to have a file that exists and
 contains this line:

\pset pager off
>>>
>>>
>>> So what does:
>>>
>>> env | grep PAGER
>>>
>>> show?
>>>
>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.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] Select works only when connected from login postgres

[Joseph Brenner]

Wait, that's not quite right.  The user 'postgres' has no PAGER envar,
but user 'doom' has an empty value:

PAGER=


On Mon, Dec 5, 2016 at 8:50 PM, Joseph Brenner  wrote:
>> So what does:
>>
>> env | grep PAGER
>>
>> show?
>
> Nothing.  I have no PAGER settting (I don't normally use one).
>
>
> On Mon, Dec 5, 2016 at 5:33 PM, Adrian Klaver  
> wrote:
>> On 12/05/2016 05:13 PM, Joseph Brenner wrote:
>>
>>
>>> I just went around temporarily undoing things I did while
>>> straigtening up, and I find there's one thing I can do that
>>> consistently breaks things: removing my new ~/.psqlrc file.
>>> In fact, it appears that I need to have a file that exists and
>>> contains this line:
>>>
>>>\pset pager off
>>
>>
>> So what does:
>>
>> env | grep PAGER
>>
>> show?
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.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] Select works only when connected from login postgres

[Joseph Brenner]

> So what does:
>
> env | grep PAGER
>
> show?

Nothing.  I have no PAGER settting (I don't normally use one).


On Mon, Dec 5, 2016 at 5:33 PM, Adrian Klaver  wrote:
> On 12/05/2016 05:13 PM, Joseph Brenner wrote:
>
>
>> I just went around temporarily undoing things I did while
>> straigtening up, and I find there's one thing I can do that
>> consistently breaks things: removing my new ~/.psqlrc file.
>> In fact, it appears that I need to have a file that exists and
>> contains this line:
>>
>>\pset pager off
>
>
> So what does:
>
> env | grep PAGER
>
> show?
>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.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] Select works only when connected from login postgres

[Tom Lane]

Joseph Brenner  writes:
> Okay: I think I'm closing in on the trouble.  I didn't used to
> have a ~/.psqlrc file, but recently I experimented with
> creating one.  When I have a .psqlrc file containing the magic
> incantaion

>\pset pager off

> *Then* everything works.

Ah!  So, most likely, there is something wrong with the local installation
of "more", or whatever the environment variable PAGER is set to.  If you
say "more somefile", does it behave reasonably?  Check "echo $PAGER"
as well.

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] Select works only when connected from login postgres

[Adrian Klaver]

On 12/05/2016 05:13 PM, Joseph Brenner wrote:



I just went around temporarily undoing things I did while
straigtening up, and I find there's one thing I can do that
consistently breaks things: removing my new ~/.psqlrc file.
In fact, it appears that I need to have a file that exists and
contains this line:

   \pset pager off


So what does:

env | grep PAGER

show?




--
Adrian Klaver
adrian.kla...@aklaver.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] Select works only when connected from login postgres

[David G. Johnston]

On Mon, Dec 5, 2016 at 6:13 PM, Joseph Brenner  wrote:

> Another oddity I noticed is that I expected that the .psqlrc
> file would not be read at all when using the --command feature,
> but instead I would see messaging that indicated the commands
> in there were being executed


​New behavior in 9.6 - you shouldn't be seeing this in your 9.4 test setup
unless you are​ using the 9.6 psql to connect to the 9.4 server (its a
client-specific behavior).

David J.

Re: [GENERAL] Select works only when connected from login postgres

[Joseph Brenner]

Okay: I think I'm closing in on the trouble.  I didn't used to
have a ~/.psqlrc file, but recently I experimented with
creating one.  When I have a .psqlrc file containing the magic
incantaion

   \pset pager off

*Then* everything works.  All three of my extant postgresql
installations work correctly whether connected to with unix
login doom or postgres.

Our story thus far:

I've got three postgresql installations running on a Debian
stable machine:

  o version 9.4, a binary package from Debian stable
(using port 5432),
  o  a build of 9.6.1 from scratch (using port 5433),
  o  an installation of 9.6.1 from a binary pgdb
 package, (using port 5434).

I've been seeing some odd behavior where a psql connection will
work fine if connected to as *unix login* 'postgres', but not
always if with unix login 'doom', it which case even the
simplest selects can fail silently, without any messages in the
log or on the screen to explain why.

Tom Lane suggested I might try connecting all three of my psql
clients to all three of the servers (by juggling the port and host
options).

Since I was going to conduct at least 9 experiments (with two
logins each), I decided to script it, but before that I made an
effort to clean things up and make sure all three installations
were exactly parallel setups: all needed a user 'doom' with
superuser privileges, all needed a 'doom' database which was
owned by 'doom', all have a pg_hba.conf with auth-method trust,
and so on.  I also added additional logging settings (as
suggested by Tom) to all three the postgresql.conf files.

There were some other small things I changed, such as making
all the log files "chmod a+r" so the script would be able to
read them and echo newly added messages...

And I created a ~/.psqlrc file, though I didn't expect it
to have any effect on my new trial runs using the --command
feature, e.g.:

  /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
--host=/var/run/postgresql --command="SELECT 'hello' AS world;"

When I got the script cleaned up and working, I found that all
9 connections worked, for both logins: something I'd done has
fixed the problem (or alternately, the problem has "gone away
on it's own").

For example, now when connecting to my local build (without
bothering to specifying port & host):

  doom@tango:~$ /usr/local/pgsql/bin/psql --dbname=doom --username=doom

This works now (as does \du, \l, etc):

  select 'hello' as world;
world
   ---
hello

I just went around temporarily undoing things I did while
straigtening up, and I find there's one thing I can do that
consistently breaks things: removing my new ~/.psqlrc file.
In fact, it appears that I need to have a file that exists and
contains this line:

   \pset pager off

I thought it might be just the fact that it was non-empty, and
tried a few other settings without any luck.  If I have that
line in my ~/.psqlrc, then this probe returns the expected result:

  /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
--host=/var/run/postgresql --command="SELECT 'hello' AS world;"

If I delete that line, then the select fails silently again.

Another oddity I noticed is that I expected that the .psqlrc
file would not be read at all when using the --command feature,
but instead I would see messaging that indicated the commands
in there were being executed, e.g.

  Pager usage is off.

Or in the logs:

  2016-12-05 16:17:04 PST [18517-3] doom@doom LOG:  statement: set
client_encoding to 'unicode'

Because I also had this line:

  \encoding unicode




On Sun, Dec 4, 2016 at 9:51 AM, Tom Lane  wrote:
> Joseph Brenner  writes:
>>> So what happens when you specify the port in your psql connection, eg:
>>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
>>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433
>>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434
>
>> With /usr/local/pgsql/bin/psql, only "-p 5433" connects, the
>> other two complain like so:
>
>>   psql: could not connect to server: No such file or directory
>> Is the server running locally and accepting
>> connections on Unix domain socket "/tmp/.s.PGSQL.5434"?
>
> What this probably indicates is that the other two installations are
> configured to put their socket files someplace else than /tmp, perhaps
> /var/run/postgresql.  Connecting to them and issuing "show
> unix_socket_directories" would tell the tale.
>
> You can persuade a psql to connect to a socket in a nondefault directory
> by giving the directory name as host, eg
>
> psql --host=/var/run/postgresql -p 5434
>
> It would be interesting to try all nine combinations of the psql's
> supplied by your various installations and the servers, just to confirm
> which ones behave normally and which don't.  Of course, the other two
> would have to be told --host=/tmp to talk to the handbuilt server.
>
> regards, tom lane

Re: [GENERAL] Select works only when connected from login postgres

[Tom Lane]

Joseph Brenner  writes:
>> So what happens when you specify the port in your psql connection, eg:
>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433
>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434

> With /usr/local/pgsql/bin/psql, only "-p 5433" connects, the
> other two complain like so:

>   psql: could not connect to server: No such file or directory
> Is the server running locally and accepting
> connections on Unix domain socket "/tmp/.s.PGSQL.5434"?

What this probably indicates is that the other two installations are
configured to put their socket files someplace else than /tmp, perhaps
/var/run/postgresql.  Connecting to them and issuing "show
unix_socket_directories" would tell the tale.

You can persuade a psql to connect to a socket in a nondefault directory
by giving the directory name as host, eg

psql --host=/var/run/postgresql -p 5434

It would be interesting to try all nine combinations of the psql's
supplied by your various installations and the servers, just to confirm
which ones behave normally and which don't.  Of course, the other two
would have to be told --host=/tmp to talk to the handbuilt server.

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] Select works only when connected from login postgres

[Tom Lane]

Adrian Klaver  writes:
> My suspicion is that when you did the source build you got some cross 
> contamination of libraries.

That's the best theory I can come up with either, although libpq's
APIs haven't really changed in any non-backwards-compatible fashion
in years.  I could imagine a newer psql flat-out crashing because it
tries to call some libpq function that doesn't exist in an older
libpq, but that's not what we're seeing here.  It's really weird.

A couple of tests that might help narrow things down:

1. In the server configuration, enable log_connections,
log_disconnections, and log_statement = 'all'.  Does anything show up in
the log when you connect with the broken psql and issue commands?

2. If you issue commands that span multiple lines --- unclosed left
parens, multiline string literals, missing semicolons --- does the
psql prompt change to match?

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] Select works only when connected from login postgres

[Joseph Brenner]

Yeah, I get the sense I need to simplify the situation, if only to
eliminate some distractions from the story.

This morning I was thinking I want to play around with pg_hba.conf
settings some more, but after that I'll do some pg_dumps and
uninstalls and see how things behave.

Oh, trying:

  /usr/bin/psql --dbname=doom --username=doom -p 543x

As login 'doom' the connection happens only for port 5432,
as login 'postgres' the connection fails differently for port 5432:

  psql: FATAL:  Peer authentication failed for user "doom"

(Like I said, I need to play with pg_hba.conf a bit.)

But that actually surprises me, I would've expected it'd be on
port 5434, because that's the most recent binary install, not the
original 9.4 version:

  /usr/bin/psql -V
  psql (PostgreSQL) 9.6.1

/etc/postgresql/9.6/main/postgresql.conf
  port = 5434# (change requires restart)

/etc/postgresql/9.4/main/postgresql.conf
  port = 5432# (change requires restart)


On Sun, Dec 4, 2016 at 7:06 AM, Adrian Klaver  wrote:
> On 12/03/2016 09:38 PM, Joseph Brenner wrote:
>>>
>>> So is the 9.4 instance the production/live database?
>>
>>
>> Essentially, but it's not heavily used: this is me messing around on a dev
>> box.
>>
>>> So what happens when you specify the port in your psql connection, eg:
>>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
>>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433
>>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434
>>
>>
>> With /usr/local/pgsql/bin/psql, only "-p 5433" connects, the
>> other two complain like so:
>>
>>   psql: could not connect to server: No such file or directory
>> Is the server running locally and accepting
>> connections on Unix domain socket "/tmp/.s.PGSQL.5434"?
>>
>
> Alright how about?:
>
> /usr/bin/psql --dbname=doom --username=doom -p 5434
>
> My suspicion is that when you did the source build you got some cross
> contamination of libraries.
>
> If it where me I would get rid of the instance that you built from source,
> assuming that there is nothing important on it.
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.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] Select works only when connected from login postgres

[Adrian Klaver]

On 12/03/2016 09:38 PM, Joseph Brenner wrote:

So is the 9.4 instance the production/live database?


Essentially, but it's not heavily used: this is me messing around on a dev box.


So what happens when you specify the port in your psql connection, eg:
/usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
/usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433
/usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434


With /usr/local/pgsql/bin/psql, only "-p 5433" connects, the
other two complain like so:

  psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5434"?



Alright how about?:

/usr/bin/psql --dbname=doom --username=doom -p 5434

My suspicion is that when you did the source build you got some cross 
contamination of libraries.


If it where me I would get rid of the instance that you built from 
source, assuming that there is nothing important on it.




--
Adrian Klaver
adrian.kla...@aklaver.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] Select works only when connected from login postgres

[Joseph Brenner]

> So is the 9.4 instance the production/live database?

Essentially, but it's not heavily used: this is me messing around on a dev box.

> So what happens when you specify the port in your psql connection, eg:
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434

With /usr/local/pgsql/bin/psql, only "-p 5433" connects, the
other two complain like so:

  psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5434"?


On Sat, Dec 3, 2016 at 9:11 PM, Adrian Klaver  wrote:
> On 12/03/2016 07:38 PM, Joseph Brenner wrote:
>>
>> Our story thus far: I've now got three different pg installations, with
>> three
>> servers running simultaneously:
>>
>> ps ax | egrep postgres | egrep '\-D'
>>   748 ?S  0:04 /usr/lib/postgresql/9.4/bin/postgres -D
>> /var/lib/postgresql/9.4/main -c
>> config_file=/etc/postgresql/9.4/main/postgresql.co
>> 23019 pts/1S  0:01 /usr/local/pgsql/bin/postgres -D
>> /usr/local/pgsql/data
>> 27352 ?S  0:00 /usr/lib/postgresql/9.6/bin/postgres -D
>> /var/lib/postgresql/9.6/main -c
>> config_file=/etc/postgresql/9.6/main/postgresql.co
>>
>> The 9.4 version presumably is using the standard default port 5432.
>
>
> So is the 9.4 instance the production/live database?
>
>> The 9.6 /usr/local version was compiled to use port 5433.
>> The other 9.6 version I just installed from apt.postgresql.org,
>> which according to the installation messages used port 5434
>> (automatically grabbing the next unused port, I gather: pretty
>> slick).
>>
>> This is what I mean by "failing silently", I get no output from
>> the select, no error message inside of psql, nothing in the error
>> logs, *but* psql doesn't terminate:
>>
>>   doom@tango:~$ /usr/local/pgsql/bin/psql --dbname=doom --username=doom
>>   psql (9.6.1)
>>   Type "help" for help.
>>
>>   doom=# select 'hello' as world;
>>   doom=#
>
>
> So what happens when you specify the port in your psql connection, eg:
>
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
>
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433
>
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434
>
>
>>
>> Nothing else gives me any output either: \l, \du, etc.
>>
 The only thing unusual about the steps that I followed was I built
 with port 5433 (rather than 5432) as the default,
>>
>>
>>> This is not as simple as it might look; the default port is actually
>>> wired into libpq.so, not psql itself.  And on most brands of Linuxen,
>>> it's not that easy to get a program to link to a non-default copy of
>>> a shared library if there's a copy in /usr/lib.  However, if you were
>>> connecting to the wrong port number, I'd still not expect that it
>>> just dies without saying anything.
>>
>>
>> Well, I've been presuming that the INSTALL file knows what
>> it's talking about in describing configure options:
>>
>>   --with-pgport=NUMBER
>>   Set "NUMBER" as the default port number for server and
>>   clients. The default is 5432. The port can always be
>>   changed later on, but if you specify it here then both
>>   server and clients will have the same default compiled in,
>>   which can be very convenient.
>
>
> Generally it is just easier/safer to just change the port in
> postgresql.conf. That is what the Debian packaging does when it sets up
> multiple Postgres instances.
>
>
>>
>>> ... maybe psql is crashing
>>> because it's linking to an ABI-incompatible libpq.  You should try
>>> "ldd" on the psql executable and see if it's resolving the libpq
>>> dependency to the copy you intended.
>>
>>
>> Ok... for /usr/local/pgsql/bin/psql this looks right, correct?
>>   /usr/local/pgsql/lib/libpq.so.5
>>
>> ldd /usr/local/pgsql/bin/psql
>> linux-vdso.so.1 (0x7fff033e2000)
>> libpq.so.5 => /usr/local/pgsql/lib/libpq.so.5 (0x7f2c34e8f000)
>> libreadline.so.6 => /lib/x86_64-linux-gnu/libreadline.so.6
>> (0x7f2c34c45000)
>> libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x7f2c34944000)
>> libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x7f2c34599000)
>> libssl.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0
>> (0x7f2c34338000)
>> libcrypto.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
>> (0x7f2c33f3c000)
>> libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0
>> (0x7f2c33d1f000)
>> libtinfo.so.5 => /lib/x86_64-linux-gnu/libtinfo.so.5
>> (0x7f2c33af5000)
>> /lib64/ld-linux-x86-64.so.2 (0x7f2c350bc000)
>> libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x7f2c338f1000)
>>
>> This seems a bit peculiar though, the binary packages are both
>> configured to use the same, unversioned libpq?
>>
>> ldd 

Re: [GENERAL] Select works only when connected from login postgres

[Adrian Klaver]

On 12/03/2016 07:38 PM, Joseph Brenner wrote:

Our story thus far: I've now got three different pg installations, with three
servers running simultaneously:

ps ax | egrep postgres | egrep '\-D'
  748 ?S  0:04 /usr/lib/postgresql/9.4/bin/postgres -D
/var/lib/postgresql/9.4/main -c
config_file=/etc/postgresql/9.4/main/postgresql.co
23019 pts/1S  0:01 /usr/local/pgsql/bin/postgres -D
/usr/local/pgsql/data
27352 ?S  0:00 /usr/lib/postgresql/9.6/bin/postgres -D
/var/lib/postgresql/9.6/main -c
config_file=/etc/postgresql/9.6/main/postgresql.co

The 9.4 version presumably is using the standard default port 5432.


So is the 9.4 instance the production/live database?


The 9.6 /usr/local version was compiled to use port 5433.
The other 9.6 version I just installed from apt.postgresql.org,
which according to the installation messages used port 5434
(automatically grabbing the next unused port, I gather: pretty
slick).

This is what I mean by "failing silently", I get no output from
the select, no error message inside of psql, nothing in the error
logs, *but* psql doesn't terminate:

  doom@tango:~$ /usr/local/pgsql/bin/psql --dbname=doom --username=doom
  psql (9.6.1)
  Type "help" for help.

  doom=# select 'hello' as world;
  doom=#


So what happens when you specify the port in your psql connection, eg:

/usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432

/usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433

/usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434




Nothing else gives me any output either: \l, \du, etc.


The only thing unusual about the steps that I followed was I built
with port 5433 (rather than 5432) as the default,



This is not as simple as it might look; the default port is actually
wired into libpq.so, not psql itself.  And on most brands of Linuxen,
it's not that easy to get a program to link to a non-default copy of
a shared library if there's a copy in /usr/lib.  However, if you were
connecting to the wrong port number, I'd still not expect that it
just dies without saying anything.


Well, I've been presuming that the INSTALL file knows what
it's talking about in describing configure options:

  --with-pgport=NUMBER
  Set "NUMBER" as the default port number for server and
  clients. The default is 5432. The port can always be
  changed later on, but if you specify it here then both
  server and clients will have the same default compiled in,
  which can be very convenient.


Generally it is just easier/safer to just change the port in 
postgresql.conf. That is what the Debian packaging does when it sets up 
multiple Postgres instances.





... maybe psql is crashing
because it's linking to an ABI-incompatible libpq.  You should try
"ldd" on the psql executable and see if it's resolving the libpq
dependency to the copy you intended.


Ok... for /usr/local/pgsql/bin/psql this looks right, correct?
  /usr/local/pgsql/lib/libpq.so.5

ldd /usr/local/pgsql/bin/psql
linux-vdso.so.1 (0x7fff033e2000)
libpq.so.5 => /usr/local/pgsql/lib/libpq.so.5 (0x7f2c34e8f000)
libreadline.so.6 => /lib/x86_64-linux-gnu/libreadline.so.6
(0x7f2c34c45000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x7f2c34944000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x7f2c34599000)
libssl.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0
(0x7f2c34338000)
libcrypto.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
(0x7f2c33f3c000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0
(0x7f2c33d1f000)
libtinfo.so.5 => /lib/x86_64-linux-gnu/libtinfo.so.5 (0x7f2c33af5000)
/lib64/ld-linux-x86-64.so.2 (0x7f2c350bc000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x7f2c338f1000)

This seems a bit peculiar though, the binary packages are both
configured to use the same, unversioned libpq?

ldd /usr/lib/postgresql/9.4/bin/psql | egrep libpq
libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5 (0x7fe9db2ea000)

ldd /usr/lib/postgresql/9.6/bin/psql | egrep libpq
libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5 (0x7fa7337ec000)

On Sat, Dec 3, 2016 at 4:51 PM, Tom Lane  wrote:

Joseph Brenner  writes:

I'm trying to get a new build of 9.6.1 working on Debian
stable and I'm seeing some odd behavior where things work
correctly if I run psql when logged in as user 'postgres',
but if I'm logged-in as user 'doom' (my usual login), I don't
seem to have any select privileges.  Even this fails
silently:



  select 'world' as hello;


Um, define "fails silently"?  Do you get a command prompt from
psql?  What does the interaction look like *exactly*?  If psql
just returns to the shell command prompt, maybe it's giving a
nonzero exit code? (try "echo $?" afterwards)

[ and later... ]


The only thing unusual about the steps that I followed was I built
with port 5433 

Re: [GENERAL] Select works only when connected from login postgres

[Joseph Brenner]

>For kicks, how about \echo or \!  Something that doesn't need a server to work.

Sure: those do work.

doom@tango:~$ /usr/local/pgsql/bin/psql --dbname=doom --username=doom
psql (9.6.1)
Type "help" for help.

doom=# select 'hello' as world;
doom=# \echo 'yo'
yo
doom=# \! ls -lad p*
drwxr-xr-x 1 doom doom 12 Nov 16 12:29 perl5

On Sat, Dec 3, 2016 at 7:48 PM, David G. Johnston
 wrote:
> On Saturday, December 3, 2016, Joseph Brenner  wrote:
>>
>>
>>   doom=# select 'hello' as world;
>>   doom=#
>>
>> Nothing else gives me any output either: \l, \du, etc.
>>
>
> For kicks, how about \echo or \!  Something that doesn't need a server to
> work.
>
> David J,


-- 
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] Select works only when connected from login postgres

[David G. Johnston]

On Saturday, December 3, 2016, Joseph Brenner  wrote:

>
>   doom=# select 'hello' as world;
>   doom=#
>
> Nothing else gives me any output either: \l, \du, etc.
>
>
For kicks, how about \echo or \!  Something that doesn't need a server to
work.

David J,

Re: [GENERAL] Select works only when connected from login postgres

[Joseph Brenner]

Our story thus far: I've now got three different pg installations, with three
servers running simultaneously:

ps ax | egrep postgres | egrep '\-D'
  748 ?S  0:04 /usr/lib/postgresql/9.4/bin/postgres -D
/var/lib/postgresql/9.4/main -c
config_file=/etc/postgresql/9.4/main/postgresql.co
23019 pts/1S  0:01 /usr/local/pgsql/bin/postgres -D
/usr/local/pgsql/data
27352 ?S  0:00 /usr/lib/postgresql/9.6/bin/postgres -D
/var/lib/postgresql/9.6/main -c
config_file=/etc/postgresql/9.6/main/postgresql.co

The 9.4 version presumably is using the standard default port 5432.
The 9.6 /usr/local version was compiled to use port 5433.
The other 9.6 version I just installed from apt.postgresql.org,
which according to the installation messages used port 5434
(automatically grabbing the next unused port, I gather: pretty
slick).

This is what I mean by "failing silently", I get no output from
the select, no error message inside of psql, nothing in the error
logs, *but* psql doesn't terminate:

  doom@tango:~$ /usr/local/pgsql/bin/psql --dbname=doom --username=doom
  psql (9.6.1)
  Type "help" for help.

  doom=# select 'hello' as world;
  doom=#

Nothing else gives me any output either: \l, \du, etc.

> > The only thing unusual about the steps that I followed was I built
> > with port 5433 (rather than 5432) as the default,

> This is not as simple as it might look; the default port is actually
> wired into libpq.so, not psql itself.  And on most brands of Linuxen,
> it's not that easy to get a program to link to a non-default copy of
> a shared library if there's a copy in /usr/lib.  However, if you were
> connecting to the wrong port number, I'd still not expect that it
> just dies without saying anything.

Well, I've been presuming that the INSTALL file knows what
it's talking about in describing configure options:

  --with-pgport=NUMBER
  Set "NUMBER" as the default port number for server and
  clients. The default is 5432. The port can always be
  changed later on, but if you specify it here then both
  server and clients will have the same default compiled in,
  which can be very convenient.

> ... maybe psql is crashing
> because it's linking to an ABI-incompatible libpq.  You should try
> "ldd" on the psql executable and see if it's resolving the libpq
> dependency to the copy you intended.

Ok... for /usr/local/pgsql/bin/psql this looks right, correct?
  /usr/local/pgsql/lib/libpq.so.5

ldd /usr/local/pgsql/bin/psql
linux-vdso.so.1 (0x7fff033e2000)
libpq.so.5 => /usr/local/pgsql/lib/libpq.so.5 (0x7f2c34e8f000)
libreadline.so.6 => /lib/x86_64-linux-gnu/libreadline.so.6
(0x7f2c34c45000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x7f2c34944000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x7f2c34599000)
libssl.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0
(0x7f2c34338000)
libcrypto.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
(0x7f2c33f3c000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0
(0x7f2c33d1f000)
libtinfo.so.5 => /lib/x86_64-linux-gnu/libtinfo.so.5 (0x7f2c33af5000)
/lib64/ld-linux-x86-64.so.2 (0x7f2c350bc000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x7f2c338f1000)

This seems a bit peculiar though, the binary packages are both
configured to use the same, unversioned libpq?

ldd /usr/lib/postgresql/9.4/bin/psql | egrep libpq
libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5 (0x7fe9db2ea000)

ldd /usr/lib/postgresql/9.6/bin/psql | egrep libpq
libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5 (0x7fa7337ec000)

On Sat, Dec 3, 2016 at 4:51 PM, Tom Lane  wrote:
> Joseph Brenner  writes:
>> I'm trying to get a new build of 9.6.1 working on Debian
>> stable and I'm seeing some odd behavior where things work
>> correctly if I run psql when logged in as user 'postgres',
>> but if I'm logged-in as user 'doom' (my usual login), I don't
>> seem to have any select privileges.  Even this fails
>> silently:
>
>>   select 'world' as hello;
>
> Um, define "fails silently"?  Do you get a command prompt from
> psql?  What does the interaction look like *exactly*?  If psql
> just returns to the shell command prompt, maybe it's giving a
> nonzero exit code? (try "echo $?" afterwards)
>
> [ and later... ]
>
>> The only thing unusual about the steps that I followed was I built
>> with port 5433 (rather than 5432) as the default,
>
> This is not as simple as it might look; the default port is actually
> wired into libpq.so, not psql itself.  And on most brands of Linuxen,
> it's not that easy to get a program to link to a non-default copy of
> a shared library if there's a copy in /usr/lib.  However, if you were
> connecting to the wrong port number, I'd still not expect that it
> just dies without saying anything.
>
> Hmm ... a different take on that is that maybe psql is 

Re: [GENERAL] Select works only when connected from login postgres

[Tom Lane]

Joseph Brenner  writes:
> I'm trying to get a new build of 9.6.1 working on Debian
> stable and I'm seeing some odd behavior where things work
> correctly if I run psql when logged in as user 'postgres',
> but if I'm logged-in as user 'doom' (my usual login), I don't
> seem to have any select privileges.  Even this fails
> silently:

>   select 'world' as hello;

Um, define "fails silently"?  Do you get a command prompt from
psql?  What does the interaction look like *exactly*?  If psql
just returns to the shell command prompt, maybe it's giving a
nonzero exit code? (try "echo $?" afterwards)

[ and later... ]

> The only thing unusual about the steps that I followed was I built
> with port 5433 (rather than 5432) as the default,

This is not as simple as it might look; the default port is actually
wired into libpq.so, not psql itself.  And on most brands of Linuxen,
it's not that easy to get a program to link to a non-default copy of
a shared library if there's a copy in /usr/lib.  However, if you were
connecting to the wrong port number, I'd still not expect that it
just dies without saying anything.

Hmm ... a different take on that is that maybe psql is crashing
because it's linking to an ABI-incompatible libpq.  You should try
"ldd" on the psql executable and see if it's resolving the libpq
dependency to the copy you intended.

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] Select works only when connected from login postgres

[Joseph Brenner]

Yes, and sorry about the re-post.  I thought my original message was
hung-up in moderation, so I was doing an unsub/resub fandango to get
email addresses to match.


On Sat, Dec 3, 2016 at 12:13 PM, Adrian Klaver
 wrote:
> On 12/03/2016 12:08 PM, Joseph Brenner wrote:
>>
>> I'm trying to get a new build of 9.6.1 working on Debian
>> stable and I'm seeing some odd behavior where things work
>> correctly if I run psql when logged in as user 'postgres',
>> but if I'm logged-in as user 'doom' (my usual login), I don't
>> seem to have any select privileges.  Even this fails
>> silently:
>>
>>   select 'world' as hello;
>>
>> But if run logged in as 'postgres', all is well:
>>
>>   sudo su - postgres
>>   /usr/local/pgsql/bin/psql --dbname=doom --username=doom
>>   doom=#   select 'world' as hello;
>> select 'world' as hello;
>> hello
>>---
>> world
>>
>> Note that I'm talking about the unix logins, in both cases
>> the postgresql username/role is 'doom' (which has Superuser
>> privileges and is the owner of the 'doom' database).
>>
>> I've got my pg_hba.conf setup to use "trust" for all users:
>>
>> # TYPE  DATABASEUSERADDRESS METHOD
>> local   all all trust
>> hostall all 127.0.0.1/32trust
>>
>> Looking at how the program files are installed, I see they're all
>> owned by 'root' with group 'staff':
>>
>>   ls -la /usr/local/pgsql/bin/psql
>>   -rwxr-xr-x 1 root staff 516824 Nov 26 23:20 /usr/local/pgsql/bin/psql
>>
>> On speculation, I added doom to the staff group and reloaded
>> pg, but that didn't help.  I see that the data files are all
>> owned by postgres, but I don't think that's unusual (or else
>> I'd try adding 'doom' to the 'postgres' group, and adding g+rwx
>> privs to the data files):
>>
>>   drwx-- 1 postgres postgres42 Nov 26 16:14 base
>>
>> I'm running out of ideas for things to check.  Any suggestions?
>>
>>
>> Some more info, for completeness sake:
>>
>> \du
>>List of roles
>>  Role name | Attributes
>>  | Member of
>>
>> ---++---
>>  doom  | Superuser, Create role, Create DB  |
>> {}
>>  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS |
>> {}
>>
>> \l
>>   List of databases
>>Name|  Owner   | Encoding |   Collate   |Ctype|
>> Access privileges
>>
>> ---+--+--+-+-+---
>>  doom  | doom | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>  postgres  | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>...
>>
>>
>
> Did you receive the previous suggestions?
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.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] Select works only when connected from login postgres

[Adrian Klaver]

On 12/03/2016 12:08 PM, Joseph Brenner wrote:

I'm trying to get a new build of 9.6.1 working on Debian
stable and I'm seeing some odd behavior where things work
correctly if I run psql when logged in as user 'postgres',
but if I'm logged-in as user 'doom' (my usual login), I don't
seem to have any select privileges.  Even this fails
silently:

  select 'world' as hello;

But if run logged in as 'postgres', all is well:

  sudo su - postgres
  /usr/local/pgsql/bin/psql --dbname=doom --username=doom
  doom=#   select 'world' as hello;
select 'world' as hello;
hello
   ---
world

Note that I'm talking about the unix logins, in both cases
the postgresql username/role is 'doom' (which has Superuser
privileges and is the owner of the 'doom' database).

I've got my pg_hba.conf setup to use "trust" for all users:

# TYPE  DATABASEUSERADDRESS METHOD
local   all all trust
hostall all 127.0.0.1/32trust

Looking at how the program files are installed, I see they're all
owned by 'root' with group 'staff':

  ls -la /usr/local/pgsql/bin/psql
  -rwxr-xr-x 1 root staff 516824 Nov 26 23:20 /usr/local/pgsql/bin/psql

On speculation, I added doom to the staff group and reloaded
pg, but that didn't help.  I see that the data files are all
owned by postgres, but I don't think that's unusual (or else
I'd try adding 'doom' to the 'postgres' group, and adding g+rwx
privs to the data files):

  drwx-- 1 postgres postgres42 Nov 26 16:14 base

I'm running out of ideas for things to check.  Any suggestions?


Some more info, for completeness sake:

\du
   List of roles
 Role name | Attributes
 | Member of
---++---
 doom  | Superuser, Create role, Create DB  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

\l
  List of databases
   Name|  Owner   | Encoding |   Collate   |Ctype|
Access privileges
---+--+--+-+-+---
 doom  | doom | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
   ...




Did you receive the previous suggestions?


--
Adrian Klaver
adrian.kla...@aklaver.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] Select works only when connected from login postgres

[Joseph Brenner]

I'm trying to get a new build of 9.6.1 working on Debian
stable and I'm seeing some odd behavior where things work
correctly if I run psql when logged in as user 'postgres',
but if I'm logged-in as user 'doom' (my usual login), I don't
seem to have any select privileges.  Even this fails
silently:

  select 'world' as hello;

But if run logged in as 'postgres', all is well:

  sudo su - postgres
  /usr/local/pgsql/bin/psql --dbname=doom --username=doom
  doom=#   select 'world' as hello;
select 'world' as hello;
hello
   ---
world

Note that I'm talking about the unix logins, in both cases
the postgresql username/role is 'doom' (which has Superuser
privileges and is the owner of the 'doom' database).

I've got my pg_hba.conf setup to use "trust" for all users:

# TYPE  DATABASEUSERADDRESS METHOD
local   all all trust
hostall all 127.0.0.1/32trust

Looking at how the program files are installed, I see they're all
owned by 'root' with group 'staff':

  ls -la /usr/local/pgsql/bin/psql
  -rwxr-xr-x 1 root staff 516824 Nov 26 23:20 /usr/local/pgsql/bin/psql

On speculation, I added doom to the staff group and reloaded
pg, but that didn't help.  I see that the data files are all
owned by postgres, but I don't think that's unusual (or else
I'd try adding 'doom' to the 'postgres' group, and adding g+rwx
privs to the data files):

  drwx-- 1 postgres postgres42 Nov 26 16:14 base

I'm running out of ideas for things to check.  Any suggestions?


Some more info, for completeness sake:

\du
   List of roles
 Role name | Attributes
 | Member of
---++---
 doom  | Superuser, Create role, Create DB  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

\l
  List of databases
   Name|  Owner   | Encoding |   Collate   |Ctype|
Access privileges
---+--+--+-+-+---
 doom  | doom | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
   ...


-- 
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] select function alias

[Merlin Moncure]

On Wed, Nov 30, 2016 at 12:15 PM, David G. Johnston
 wrote:
> On Wed, Nov 30, 2016 at 8:15 AM, bto...@computer.org
>  wrote:
>>
>> 5. Use a CTE:
>>
>> with shortnames as (
>> select to_char(impressions_create_date,'-mm-dd') as ymd from
>> impressionsdb
>> ) select ymd from shortnames where ymd like '2016-11%';
>>
>
> Except that WHERE clause won't get pushed down (I think...haven't checked an
> explain recently) so a sub-select is definitely better in this scenario.

It's still the case FWICT.  I suspect CTE optimization fencing is
mostly considered a feature, not a bug.

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] select function alias

[David G. Johnston]

On Wed, Nov 30, 2016 at 8:15 AM, bto...@computer.org  wrote:

> 5. Use a CTE:
>
> with shortnames as (
> select to_char(impressions_create_date,'-mm-dd') as ymd from
> impressionsdb
> ) select ymd from shortnames where ymd like '2016-11%';
>
>
​Except that WHERE clause won't get pushed down (I think...haven't checked
an explain recently) so a sub-select is definitely better in this scenario.

David J.

Re: [GENERAL] select function alias

[bto...@computer.org]

- Original Message -

> From: "Howard News" <howardn...@selestial.com>
> Sent: Wednesday, November 30, 2016 9:03:28 AM
> Subject: Re: [GENERAL] select function alias

> On 30/11/2016 13:42, Timoteo Blanco wrote:

> > I've a series of timestamp columns I'd like to alias in select statements.
> > psql indicates my alias doesnt exist after
> 
> > I define it.
> 

> > Example -> select to_char(impressions_create_date,'-mm-dd') as ymd from
> > impressionsdb where ymd like '2016-11%' ;
> 

> > psql always complains column ymd does not exist. I've inherited a series of
> > tables with many timestamps and would like to
> 
> > shorten the select in queries without renaming any columns.
> 

> 3 options:

> 1. Use the same to_char expression in the where clause
> 2. Use a sub-select to use the alias in the outer where clause
> 3. Use the original column in the where clause and use the timestamp
> comparisson functions.

4. Define views on the tables. Make the views writeable using rules. 

5. Use a CTE: 

with shortnames as ( 
select to_char(impressions_create_date,'-mm-dd') as ymd from impressionsdb 
) select ymd from shortnames where ymd like '2016-11%'; 

-- B 

Re: [GENERAL] select function alias

[Howard News]

On 30/11/2016 13:42, Timoteo Blanco wrote:

Howdy,

I've a series of timestamp columns I'd like to alias in select 
statements. psql indicates my alias doesnt exist after

I define it.

Example -> select to_char(impressions_create_date,'-mm-dd') as ymd 
from impressionsdb where ymd like '2016-11%' ;


psql always complains column ymd does not exist. I've inherited a 
series of tables with many timestamps and would like to

shorten the select in queries without renaming any columns.


postgresql 9.2 on Gentoo 4.4.26

TIA, TRB



3 options:

1. Use the same to_char expression in the where clause
2. Use a sub-select to use the alias in the outer where clause
3. Use the original column in the where clause and use the timestamp
   comparisson functions.

Howard.

[GENERAL] select function alias

[Timoteo Blanco]

Howdy,

I've a series of timestamp columns I'd like to alias in select statements.
psql indicates my alias doesnt exist after
I define it.

Example -> select to_char(impressions_create_date,'-mm-dd') as ymd from
impressionsdb where ymd like '2016-11%' ;

psql always complains column ymd does not exist. I've inherited a series of
tables with many timestamps and would like to
shorten the select in queries without renaming any columns.


postgresql 9.2 on Gentoo 4.4.26

TIA, TRB

Re: [GENERAL] SELECT DISTINCT ON removes results

[Geoff Winkless]

On 28 October 2016 at 21:39, Guyren Howe  wrote:

> Using 9.5, this query:
>
> SELECT o.id,
>a.number AS awb
>   FROM pt.orders o
> LEFT JOIN (
> SELECT DISTINCT ON ((string_agg(air_way_bills.number::text, 
> ','::text)))
>   string_agg(air_way_bills.number::text, ','::text) AS number,
>air_way_bills.order_id
>   FROM pt.air_way_bills
>  GROUP BY air_way_bills.order_id) a ON a.order_id = o.id
>
> ​From https://www.postgresql.org/docs/9.5/static/sql-select.
html#SQL-DISTINCT​

​
The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s).
The ORDER BY clause will normally contain additional expression(s) that
determine the desired precedence of rows within each DISTINCT ON group.
​


​Does the problem go away if you do that?​

​Geoff​

Re: [GENERAL] SELECT DISTINCT ON removes results

[David G. Johnston]

On Fri, Oct 28, 2016 at 2:17 PM, Guyren Howe  wrote:

> On Oct 28, 2016, at 14:15 , Guyren Howe  wrote:
>
>
> On Oct 28, 2016, at 13:50 , David G. Johnston 
> wrote:
>
>
> ​On its face the statement "DISTINCT ON removes results" is not at all
> surprising given its definition.
>
>
> What is surprising is that it removes *all* results…
>
>
> The inner query on its own appears to produce the same results, but the
> left join with it in either case produces different results.
>

​Then maybe you can help us and create reproducible example that
demonstrates the bug so that it can be fixed.

David J.
​

Re: [GENERAL] SELECT DISTINCT ON removes results

[Guyren Howe]

> On Oct 28, 2016, at 13:50 , David G. Johnston  
> wrote:
> 
> On Fri, Oct 28, 2016 at 1:39 PM, Guyren Howe  > wrote:
> Using 9.5, this query:

Unless I'm missing something, this ought to be impossible.

Two queries differing only in having a DISTINCT clause produce the same result, 
demonstrated by EXCEPT:
=> SELECT DISTINCT ON ((string_agg(air_way_bills.number::text, ','::text)))
->string_agg(air_way_bills.number::text, ','::text) AS 
number,
-> air_way_bills.order_id
->FROM pt.air_way_bills
-> where air_way_bills.order_id = 2792
->   GROUP BY air_way_bills.order_id
-> except
-> select string_agg(air_way_bills.number::text, ','::text) AS number,
-> air_way_bills.order_id
->FROM pt.air_way_bills
-> where air_way_bills.order_id = 2792
->   GROUP BY air_way_bills.order_id;
 number | order_id 
+--
(0 rows)

but joining with them produces different results:
=> SELECT o.id,
->a.number AS awb
->   FROM pt.orders o
-> LEFT JOIN (
(> SELECT
(>   string_agg(air_way_bills.number::text, ','::text) AS 
number,
(>air_way_bills.order_id
(>   FROM pt.air_way_bills
(>  GROUP BY air_way_bills.order_id) a ON a.order_id = o.id
-> where o.id = 2792;
  id  |   awb
--+--
 2792 | 91484540
(1 row)

=> SELECT o.id,
->a.number AS awb
->   FROM pt.orders o
-> LEFT JOIN (
(> SELECT DISTINCT ON ((string_agg(air_way_bills.number::text, 
','::text)))
(>   string_agg(air_way_bills.number::text, ','::text) AS 
number,
(>air_way_bills.order_id
(>   FROM pt.air_way_bills
(>  GROUP BY air_way_bills.order_id) a ON a.order_id = o.id
-> where o.id = 2792
  id  | awb 
--+-
 2792 | 
(1 row)

Re: [GENERAL] SELECT DISTINCT ON removes results

[Guyren Howe]

On Oct 28, 2016, at 14:15 , Guyren Howe  wrote:
> 
> On Oct 28, 2016, at 13:50 , David G. Johnston  > wrote:
>> 
>> ​On its face the statement "DISTINCT ON removes results" is not at all 
>> surprising given its definition.
> 
> What is surprising is that it removes *all* results…

The inner query on its own appears to produce the same results, but the left 
join with it in either case produces different results.

Re: [GENERAL] SELECT DISTINCT ON removes results

[Guyren Howe]

On Oct 28, 2016, at 13:50 , David G. Johnston  
wrote:
> 
> ​On its face the statement "DISTINCT ON removes results" is not at all 
> surprising given its definition.

What is surprising is that it removes *all* results…

Re: [GENERAL] SELECT DISTINCT ON removes results

[David G. Johnston]

On Fri, Oct 28, 2016 at 1:39 PM, Guyren Howe  wrote:

> Using 9.5, this query:
>
> SELECT o.id,
>a.number AS awb
>   FROM pt.orders o
> LEFT JOIN (
> SELECT DISTINCT ON ((string_agg(air_way_bills.number::text, 
> ','::text)))
>   string_agg(air_way_bills.number::text, ','::text) AS number,
>air_way_bills.order_id
>   FROM pt.air_way_bills
>  GROUP BY air_way_bills.order_id) a ON a.order_id = o.id
>
> gives me null for awb. Removing the DISTINCT ON clause:
>
> SELECT o.id,
>a.number AS awb
>   FROM pt.orders o
> LEFT JOIN (
> SELECT string_agg(air_way_bills.number::text, ','::text) AS 
> number,
>air_way_bills.order_id
>   FROM pt.air_way_bills
>  GROUP BY air_way_bills.order_id) a ON a.order_id = o.id
> where o.id = 2792;
>
> gives me an awb. I'm confused about how this can be.
>


​On its face the statement "DISTINCT ON removes results" is not at all
surprising given its definition.

Given a self-contained query exhibiting the desired behavior I might be
willing to figure out and explain exactly why its happening in that
particular circumstance.

Oh, and on its face your DISTINCT ON query doesn't make any sense to me.
Using DISTINCT ON on one column but then joining on the discard-able ID
column is...unusual.

And it also lacks an ORDER BY for deterministic discarding of duplicate
rows.

David J.

[GENERAL] SELECT DISTINCT ON removes results

[Guyren Howe]

Using 9.5, this query:

SELECT o.id,
   a.number AS awb
  FROM pt.orders o
LEFT JOIN (
SELECT DISTINCT ON ((string_agg(air_way_bills.number::text, 
','::text)))
  string_agg(air_way_bills.number::text, ','::text) AS number,
   air_way_bills.order_id
  FROM pt.air_way_bills
 GROUP BY air_way_bills.order_id) a ON a.order_id = o.id
gives me null for awb. Removing the DISTINCT ON clause:

SELECT o.id,
   a.number AS awb
  FROM pt.orders o
LEFT JOIN (
SELECT string_agg(air_way_bills.number::text, ','::text) AS 
number,
   air_way_bills.order_id
  FROM pt.air_way_bills
 GROUP BY air_way_bills.order_id) a ON a.order_id = o.id
where o.id = 2792;
gives me an awb. I'm confused about how this can be.

Re: [GENERAL] select distinct postgres 9.2

[Patrick B]

2016-09-19 9:18 GMT+12:00 Patrick B :

> Hi guys,
>
> I've got the following query:
>
> WITH
>>   accounts AS (
>> SELECT
>> c.id AS company_id,
>> c.name_first AS c_first_name,
>> c.name_last AS c_last_name,
>> c.company AS c_name,
>> FROM public.clients c
>> WHERE id = 33412393
>> ORDER BY 1 LIMIT 100
>> )
>> SELECT
>> r.parts[4]::INT AS account_id,
>> r.parts[6]::INT AS n_id,
>> r.parts[9] AS variation,
>> size,
>> FROM (
>> SELECT
>>   string_to_array(full_path, '/') AS parts,
>>   size
>>   FROM public.segments s
>>   WHERE public.f_get_account_from_full_path(s.full_path) IN (SELECT
>> company_id FROM accounts)
>> ) r
>
>
> ... and I want to get only the greatest note_id order by size,
>
> How can I put this query into the above one?
>
>> SELECT DISTINCT ON
>> (n_id) n_id,
>> MAX(size)
>> FROM
>> test1
>> GROUP BY
>> note_id, size, st_ino, account_id
>> ORDER BY
>> note_id, size desc
>
>
> DISTINCT ON (r.parts[6]::INT) AS n_id - it doesn't work...
>
>
> Thanks
> Patrick
>



Actually.. I was able to get what I needed doing:


WITH
>   accounts AS (
> SELECT
> c.id AS company_id,
> c.name_first AS c_first_name,
> c.name_last AS c_last_name,
> c.company AS c_name,
> FROM public.clients c
> WHERE id = 33412393
> ORDER BY 1 LIMIT 100
> )
> SELECT DISTINCT ON
> (r.parts[6]::INT) r.parts[6]::INT AS n_id,
> r.parts[4]::INT AS account_id,
> r.parts[9] AS variation,
> size,
> FROM (
> SELECT
>   string_to_array(full_path, '/') AS parts,
>   size
>   FROM public.segments s
>   WHERE public.f_get_account_from_full_path(s.full_path) IN (SELECT
> company_id FROM accounts)
> ) r



Thanks guys!

[GENERAL] select distinct postgres 9.2

[Patrick B]

Hi guys,

I've got the following query:

WITH
>   accounts AS (
> SELECT
> c.id AS company_id,
> c.name_first AS c_first_name,
> c.name_last AS c_last_name,
> c.company AS c_name,
> FROM public.clients c
> WHERE id = 33412393
> ORDER BY 1 LIMIT 100
> )
> SELECT
> r.parts[4]::INT AS account_id,
> r.parts[6]::INT AS n_id,
> r.parts[9] AS variation,
> size,
> FROM (
> SELECT
>   string_to_array(full_path, '/') AS parts,
>   size
>   FROM public.segments s
>   WHERE public.f_get_account_from_full_path(s.full_path) IN (SELECT
> company_id FROM accounts)
> ) r


... and I want to get only the greatest note_id order by size,

How can I put this query into the above one?

> SELECT DISTINCT ON
> (n_id) n_id,
> MAX(size)
> FROM
> test1
> GROUP BY
> note_id, size, st_ino, account_id
> ORDER BY
> note_id, size desc


DISTINCT ON (r.parts[6]::INT) AS n_id - it doesn't work...


Thanks
Patrick

Re: [GENERAL] select date between - PostgreSQL 9.5

[Daevor The Devoted]

On Wed, Sep 14, 2016 at 4:49 AM, Patrick B  wrote:

>
>
> 2016-09-14 13:17 GMT+12:00 David Rowley :
>
>> On 14 September 2016 at 12:20, Patrick B 
>> wrote:
>> > I want to select all rows that have been modified from now to 4 months
>> ago.
>> >
>> > I've used these queries:
>> >
>> >> select
>> >> modified_date,
>> >> from
>> >> clients
>> >> WHERE
>> >> modified_date BETWEEN '2016-06-13' AND '2016-09-13'
>> >
>>
>> Going by my clock here 2016-06-13 was just over 3 months ago, not 4.
>>
>>
>> >> select
>> >> modified_date,
>> >> from
>> >> clients
>> >> WHERE
>> >> modified_date >='2016-06-13' AND modified_date < '2016-09-13'
>> >
>> >
>> >
>> > But it didn't work... it returns 0 rows but there are rows to be
>> shown:
>> >
>> >
>> >> select modified_date from clients ORDER BY modified_date ASC
>> >
>> >
>> >
>> >> modified_date
>> >> ---
>> >> 2015-07-11 17:23:40
>> >> 2016-09-13 20:00:51
>> >> 2016-09-13 20:00:51
>> >> 2016-09-13 20:00:51
>> >> 2016-09-13 20:00:51
>> >
>> >
>> >
>> > What am I doing wrong?
>>
>> None of those dates are between your specified date range. If you want
>> to include all of 2016-09-13 timestamps, then you'd better do <
>> '2016-09-14' since < '2016-09-13' will only cover timestamps on the
>> 12th or before.
>>
>>
>> --
>>  David Rowley   http://www.2ndQuadrant.com/
>>  PostgreSQL Development, 24x7 Support, Training & Services
>>
>
>
> Thanks guys...
>
> I've used < and >
>
> not sure why wasn't working before :(
>
> Thanks!
> Patrick
>


It didn't work before because, as excellently pointed out by Vitaly
Burovoy, because

modified_date BETWEEN '2016-06-13' AND '2016-09-13'

is evaluated as

modified_date >= '2016-06-13 00:00:00' AND modified_date <= '2016-09-13
00:00:00'

None of your timestamps falls in that range. '2016-09-13 20:00:51'  is 20
hours and 51 seconds after the end of this range, and '2015-07-11 17:23:40'
is more than a year before it.

Similar logic applies to modified_date >= '2016-06-13 00:00:00' AND
modified_date
< '2016-09-13 00:00:00'

Now, the reason it is working for you now, is probably because you're in a
timezone where it is already 2016-09-14, and your WHERE clause now reads:

modified_date >= '2016-06-14 00:00:00' AND modified_date < '2016-09-14
00:00:00'

with the effect that the timestamp '2016-09-13 20:00:51' now falls within
the range of your new WHERE clause.

At least, that's my suspicion.

Kind regards,
Na-iem Dollie

Re: [GENERAL] select date between - PostgreSQL 9.5

[Patrick B]

2016-09-14 13:17 GMT+12:00 David Rowley :

> On 14 September 2016 at 12:20, Patrick B  wrote:
> > I want to select all rows that have been modified from now to 4 months
> ago.
> >
> > I've used these queries:
> >
> >> select
> >> modified_date,
> >> from
> >> clients
> >> WHERE
> >> modified_date BETWEEN '2016-06-13' AND '2016-09-13'
> >
>
> Going by my clock here 2016-06-13 was just over 3 months ago, not 4.
>
>
> >> select
> >> modified_date,
> >> from
> >> clients
> >> WHERE
> >> modified_date >='2016-06-13' AND modified_date < '2016-09-13'
> >
> >
> >
> > But it didn't work... it returns 0 rows but there are rows to be
> shown:
> >
> >
> >> select modified_date from clients ORDER BY modified_date ASC
> >
> >
> >
> >> modified_date
> >> ---
> >> 2015-07-11 17:23:40
> >> 2016-09-13 20:00:51
> >> 2016-09-13 20:00:51
> >> 2016-09-13 20:00:51
> >> 2016-09-13 20:00:51
> >
> >
> >
> > What am I doing wrong?
>
> None of those dates are between your specified date range. If you want
> to include all of 2016-09-13 timestamps, then you'd better do <
> '2016-09-14' since < '2016-09-13' will only cover timestamps on the
> 12th or before.
>
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>


Thanks guys...

I've used < and >

not sure why wasn't working before :(

Thanks!
Patrick

Re: [GENERAL] select date between - PostgreSQL 9.5

[David Rowley]

On 14 September 2016 at 12:20, Patrick B  wrote:
> I want to select all rows that have been modified from now to 4 months ago.
>
> I've used these queries:
>
>> select
>> modified_date,
>> from
>> clients
>> WHERE
>> modified_date BETWEEN '2016-06-13' AND '2016-09-13'
>

Going by my clock here 2016-06-13 was just over 3 months ago, not 4.


>> select
>> modified_date,
>> from
>> clients
>> WHERE
>> modified_date >='2016-06-13' AND modified_date < '2016-09-13'
>
>
>
> But it didn't work... it returns 0 rows but there are rows to be shown:
>
>
>> select modified_date from clients ORDER BY modified_date ASC
>
>
>
>> modified_date
>> ---
>> 2015-07-11 17:23:40
>> 2016-09-13 20:00:51
>> 2016-09-13 20:00:51
>> 2016-09-13 20:00:51
>> 2016-09-13 20:00:51
>
>
>
> What am I doing wrong?

None of those dates are between your specified date range. If you want
to include all of 2016-09-13 timestamps, then you'd better do <
'2016-09-14' since < '2016-09-13' will only cover timestamps on the
12th or before.


-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] select date between - PostgreSQL 9.5

[Vitaly Burovoy]

On 9/13/16, Patrick B  wrote:
> Hi guys,
>
> I got the following column:
>
> modified_date TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT
>> "statement_timestamp"(),
>
>
> I want to select all rows that have been modified from now to 4 months ago.
>
> I've used these queries:
>
> select
>> modified_date,
>> from
>> clients
>> WHERE
>> modified_date BETWEEN '2016-06-13' AND '2016-09-13'

Note that '2016-09-13' is not "now", it is converted to the data type
of a column (expression):
Your expression "modified_date BETWEEN '2016-06-13' AND '2016-09-13'"
means a little different:

The best way to understand it - to use explain:

postgres=# EXPLAIN select modified_date from clients WHERE
modified_date BETWEEN '2016-06-13' AND '2016-09-13';

  QUERY PLAN
-
 Seq Scan on clients  (cost=0.00..43.90 rows=11 width=8)
   Filter: ((modified_date >= '2016-06-13 00:00:00'::timestamp without
time zone) AND (modified_date <= '2016-09-13 00:00:00'::timestamp
without time zone))
(2 rows)

It is not good to use BETWEEN with timestamps (not dates) because in
your example only one exact value (exact to milliseconds) from the
'2016-09-13' will be returned.
2016-09-12 23:59:59.98 (yes)
2016-09-12 23:59:59.99 (yes)
2016-09-13 00:00:00.00 (yes)  <<< the only value from this date
2016-09-13 00:00:00.01 (no)
2016-09-13 00:00:00.02 (no)
etc.

Note that even if you rewrite as "modified_date BETWEEN
'2016-06-13'::date AND '2016-09-13'::date" you still get the same
result because less accuracy type is converting to a type with bigger
accuracy, i.e. to timestamp, not to date.

When you work with timestamps the best way is to use direct "min_value
<= column and column < max_value" (with open upper bound) rather than
"between" statement.

> and
>
>
>> select
>> modified_date,
>> from
>> clients
>> WHERE
>> modified_date >='2016-06-13' AND modified_date < '2016-09-13'
>
>
>
> But it didn't work... it returns 0 rows but there are rows to be shown:
>
>
> select modified_date from clients ORDER BY modified_date ASC
>
>
> modified_date
>> ---
>> 2015-07-11 17:23:40
  it is 2015 year, more than 1 year ago

vv because expression is rewritten as "modified_date <= 2016-09-13
00:00:00", less than your values
>> 2016-09-13 20:00:51
>> 2016-09-13 20:00:51
>> 2016-09-13 20:00:51
>> 2016-09-13 20:00:51
>
> What am I doing wrong?
> Cheers
> Patrick

-- 
Best regards,
Vitaly Burovoy


-- 
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] select date between - PostgreSQL 9.5

[Adrian Klaver]

On 09/13/2016 05:20 PM, Patrick B wrote:

Hi guys,

I got the following column:

modified_date TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT
"statement_timestamp"(),


I want to select all rows that have been modified from now to 4 months ago.

I've used these queries:

select
modified_date,
from
clients
WHERE
modified_date BETWEEN '2016-06-13' AND '2016-09-13'


and


select
modified_date,
from
clients
WHERE
modified_date >='2016-06-13' AND modified_date < '2016-09-13'



But it didn't work... it returns 0 rows but there are rows to be shown:


select modified_date from clients ORDER BY modified_date ASC



modified_date
---
2015-07-11 17:23:40
2016-09-13 20:00:51
2016-09-13 20:00:51
2016-09-13 20:00:51
2016-09-13 20:00:51



What am I doing wrong?


test=> select '2016-09-13'::timestamp; 



  timestamp 



- 



 2016-09-13 00:00:00


So either:

test=> select '2016-09-13 20:00:51'::date between '09/11/2016' and 
'09/13/2016';

 ?column?
--
 t
(1 row)


or

test=> select '2016-09-13 20:00:51' between '09/11/2016'::timestamp and 
'09/14/2016'::timestamp;

 ?column?
--
 t




Cheers
Patrick



--
Adrian Klaver
adrian.kla...@aklaver.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] select date between - PostgreSQL 9.5

[Patrick B]

Hi guys,

I got the following column:

modified_date TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT
> "statement_timestamp"(),


I want to select all rows that have been modified from now to 4 months ago.

I've used these queries:

select
> modified_date,
> from
> clients
> WHERE
> modified_date BETWEEN '2016-06-13' AND '2016-09-13'


and


> select
> modified_date,
> from
> clients
> WHERE
> modified_date >='2016-06-13' AND modified_date < '2016-09-13'



But it didn't work... it returns 0 rows but there are rows to be shown:


select modified_date from clients ORDER BY modified_date ASC



modified_date
> ---
> 2015-07-11 17:23:40
> 2016-09-13 20:00:51
> 2016-09-13 20:00:51
> 2016-09-13 20:00:51
> 2016-09-13 20:00:51



What am I doing wrong?
Cheers
Patrick

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

[Alexander Farber]

Thank you Craig, this has worked in my custom function too:

BEGIN
PERFORM check_positions(in_uid, in_gid, in_tiles);

CREATE TEMP TABLE _words ON COMMIT DROP AS
SELECT
out_word AS word,
max(out_score) AS score
FROM check_words(in_uid, in_gid, in_tiles)
GROUP BY word, gid;

PL/pgSQL is weird, but fun :-)

I like that I can RAISE EXCEPTION in my custom function and PostgreSQL
rolls everything back.

Regards
Alex

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

[Craig Ringer]

On 12 August 2016 at 18:43, Alexander Farber 
wrote:

> Thank you,  I have rewritten it into:
>
> BEGIN
> PERFORM check_positions(in_uid, in_gid, in_tiles);
>
> CREATE TEMP TABLE _words(word varchar, score integer) ON COMMIT
> DROP;
>
> INSERT INTO _words
> SELECT
> out_word AS word,
> max(out_score) AS score
> FROM check_words(in_uid, in_gid, in_tiles)
> GROUP BY word, gid;
>
>
Or use CREATE TABLE ... AS SELECT ...

That's the SQL-standard spelling anyway.


-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

[Alexander Farber]

Thank you,  I have rewritten it into:

BEGIN
PERFORM check_positions(in_uid, in_gid, in_tiles);

CREATE TEMP TABLE _words(word varchar, score integer) ON COMMIT
DROP;

INSERT INTO _words
SELECT
out_word AS word,
max(out_score) AS score
FROM check_words(in_uid, in_gid, in_tiles)
GROUP BY word, gid;

Regards
Alex

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

[Guillaume Lelarge]

2016-08-12 11:00 GMT+02:00 Alexander Farber :

> Francisco, thanks, but -
>
> On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte  > wrote:
>
>>
>> https://www.postgresql.org/docs/9.5/static/plpgsql-statement
>> s.html#PLPGSQL-STATEMENTS-SQL-ONEROW
>>
>>
> but the custom function I am trying to call (from another function) does
> not return one row, but several rows, which I'd like to store into a temp
> table:
>
> SELECT
> out_word AS word,
> max(out_score) AS score
> INTO TEMP TABLE _words ON COMMIT DROP
> FROM check_words(in_uid, in_gid, in_tiles)
> GROUP BY word, gid;
>
>
Francisco is right. SELECT INTO doesn't have the same meaning in SQL and
PL/pgsql. If you want to insert the result of the SELECT into a temporary
table, create the temp table and insert into it:

CREATE TEMP TABLE...
INSERT INTO your_temp_table SELECT...


-- 
Guillaume.
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

[Francisco Olarte]

Alexander:

On Fri, Aug 12, 2016 at 11:00 AM, Alexander Farber
 wrote:
> but the custom function I am trying to call (from another function) does not
> return one row, but several rows, which I'd like to store into a temp table:

This I know, I wasn't trying to solve the problem. I was just trying
to point that "select" is not the same in plpgsql and in sql, so you
need to read the docs for plpgsql to find how to solve it.

Francisco Olarte.


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