Re: Space Stalker in SQL Output

[Joe Conway]

On 06/27/2018 12:45 PM, Susan Hurst wrote:
> Wow!  The -A option worked perfectly!
> 
> Thanks for the syntax lesson Steve and Jerry!

If you are going to be doing lots of scripting with Postgres, you might
want to take a look here: https://github.com/jconway/shebang

HTH,

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data

[Adrian Klaver]

On 06/27/2018 09:55 AM, Moreno Andreo wrote:

Il 22/06/2018 19:56, Adrian Klaver ha scritto:

On 06/22/2018 09:50 AM, Moreno Andreo wrote:

Il 22/06/2018 15:18, Adrian Klaver ha scritto:






I am at a loss now. The only thing I can think of is that data itself 
is actually corrupted. Maybe some sort of language encoding/collation 
issue. Just not sure how to test that at the moment.


Actually, I tried it in a bunch of other fields with varying data types 
and everything went fine.
I don't know if it's as you say and I mismatched keys (and I need 
another pair of glasses) or something else. Just hoping (but being 
confident) it won't happen again.


You might try emailing the pgcrypto author
https://www.postgresql.org/docs/10/static/pgcrypto.html#id-1.11.7.35.11

In the source code I noticed that there are many:

px_debug('Some text')

that map to:

{PXE_PGP_CORRUPT_DATA, "Wrong key or corrupt data"}.

I tried running with messages set to DEBUG to see if I could get at the 
more specific messages. That did not work, so you might ask the author 
if there is a way to get at them.




Now trying to speed up a little some queries involving SELECTing among 
these encrypted fields, if I'm stuck I'll open a new thread.


Thanks,
Moreno.-








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

Re: Database name with semicolon

[Adrian Klaver]

On 06/27/2018 06:22 AM, Joby John wrote:

Hi,

We have a windows application which communicate to Postgres database via 
PostgreSQL ODBC drivers for windows.


One of our customers has a database with semicolon in its name (e.g.: 
“db; name”) and our application is failing to establish connection to 
that database saying like it could not find the database.


We tried all known possibilities by enclosing the DB name under quotes, 
curly brackets etc. but none solved the problem.


I wonder whether there is any possible way to achieve connection to such 
database. If not, probably it’s better not to support semicolons within 
the database name.


https://msdn.microsoft.com/en-us/library/system.data.odbc.odbcconnection.connectionstring(v=vs.110).aspx

"
...

Applications do not have to add braces around the attribute value after 
the Driver keyword unless the attribute contains a semicolon (;), in 
which case the braces are required. If the attribute value that the 
driver receives includes braces, the driver should not remove them but 
they should be part of the returned connection string.


...

"

See if something like below works:

{db; name}



Not sure whether I need to submit this query as a bug or not. Any help 
is much appreciated.


Thanks and Regards,

Joby John


Joby John
Software Developer
NCC Group
Kings Court, Kingston Road, Leatherhead, KT22 7SL

Telephone: +44 1372 383 800 
Mobile: 
Website: www.nccgroup.trust 
Twitter: @NCCGroupplc 




This email is sent for and on behalf of NCC Group. NCC Group is the 
trading name of NCC Services Limited (Registered in England CRN: 
2802141). The ultimate holding company is NCC Group plc (Registered in 
England CRN: 4627044). This email may be confidential and/or legally 
privileged.





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

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

[Andres Freund]

Hi,

On 2018-06-27 20:35:16 +0200, Luca Ferrari wrote:
> I've found this strange (to me) behavior when doing nasty things with
> indexes and immutable functions:
> 
> create table t( pk serial, t text );
> insert into t( t ) values( 'hello' ), ('world');
> create or replace function f_fake( i int )
> returns text
> as $body$
> declare
>   v_t text;
> begin
>select t into strict v_t
>from t where pk = i limit 1;
>return v_t;
> exception
>   when no_data_found then return 'a';
> end
> $body$
> language plpgsql immutable;
> 
> Of course, f_fake is not immutable.
> When on 10.4 or 11 beta 1 I try to create an index on this nasty
> crappy function:
> 
> create index idx_fake on t ( f_fake( pk ) );
> 
> ERROR:  could not read block 0 in file "base/16392/16444": read only 0
> of 8192 bytes
> CONTEXT:  SQL statement "select tfrom t where pk =
> i limit 1"
> PL/pgSQL function f_fake(integer) line 5 at SQL statement

> that is somehow correct (because the function cannot be used to build
> an index), but then it goes worst:
> 
> elect * from t;
> ERROR:  could not open relation with OID 16444

That certainly isn't behaviour I'd expect. Doing nasty stuff inside an
immutable function will have bad consequences, but the permanent failure
shouldn't be there.

But I also can't reproduce it either on 10.4, 10-current, master.  Did
you build from source? Packages? Any extensions? Is there anything
missing from the above instruction to reproduce this?

Greetings,

Andres Freund

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

[Peter Geoghegan]

On Wed, Jun 27, 2018 at 11:35 AM, Luca Ferrari  wrote:
> If I then disconnect and reconnect I'm able to issue the select and
> get back the results. But if I issue a reindex I got the same error
> and the table "becames unreadable" for the whole session.
> On 10.3 the table is never locked for the session, that is I can
> create the index, I can query the table and get the results, but I
> cannot reindex. However, even after a reindex, it does allow me to
> select data from the table.
>
> So my question is: why this behavior in later PostgreSQL?

It might have something to do with the changes to parallel CREATE
INDEX. It changed how we tracked whether or not an index could be used
because it was currently undergoing reindexing. This is supposed to
make no difference at all, but there was one bug that could cause us
to consider an index irrevocably unusable.

Do you find that the issue goes away if you set
max_parallel_maintenance_workers=0 on v11/master?

-- 
Peter Geoghegan

Re: Space Stalker in SQL Output

[Susan Hurst]

Wow!  The -A option worked perfectly!

Thanks for the syntax lesson Steve and Jerry!

Sue

---

Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hu...@brookhurstdata.com
Mobile: 314-486-3261

On 2018-06-27 14:38, Jerry Sievers wrote:

Susan Hurst  writes:


Why would a psql statement insert a leading space into the output,
which is a single integer value?

The leading space caused my job call to fail elsewhere in the same
shell script as the psql call.  Here is the anonymized version of the
psql call to assign a value to a shell script variable:

IDz=`psql -d proddb -U produser -h 10.9.999.99 -p 99900 -t <
last_id.sql`


Get in the habit of including -A which gets rid of alignment padding in
psql output.

As in...

shellvar=`psql -Atqc 'select froboz;'` $db

HTH

Re: Space Stalker in SQL Output

[Jerry Sievers]

Susan Hurst  writes:

> Why would a psql statement insert a leading space into the output,
> which is a single integer value?
>
> The leading space caused my job call to fail elsewhere in the same
> shell script as the psql call.  Here is the anonymized version of the
> psql call to assign a value to a shell script variable:
>
> IDz=`psql -d proddb -U produser -h 10.9.999.99 -p 99900 -t <
> last_id.sql`

Get in the habit of including -A which gets rid of alignment padding in
psql output.

As in...

shellvar=`psql -Atqc 'select froboz;'` $db

HTH

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800

Space Stalker in SQL Output

[Susan Hurst]

Why would a psql statement insert a leading space into the output, which 
is a single integer value?


The leading space caused my job call to fail elsewhere in the same shell 
script as the psql call.  Here is the anonymized version of the psql 
call to assign a value to a shell script variable:


IDz=`psql -d proddb -U produser -h 10.9.999.99 -p 99900 -t < 
last_id.sql`


The output is simply a max(id) value, which is defined as an integer 
data type in the source table column.  The output looked like this 
(notice the leading space before the integer value):


echo “IDz =${IDz}
IDz =’ 100’

The last_id.sql itself is simply:  select max(id) from prodtable;

I'm using:

PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 
20120313 (Red Hat 4.4.7-16), 64-bit


I fixed the output in the shell script with a tr command but why should 
that be necessary?  What is causing the space to be prepended to integer 
value?



ID=`echo ${IDz} | tr -d ''`
IDz =’100’

Knowing the root cause of the space stalker would be most helpful.  
Thanks for your help!


Sue


--

Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hu...@brookhurstdata.com
Mobile: 314-486-3261

Re: Database name with semicolon

[Vick Khera]

On Wed, Jun 27, 2018 at 10:25 AM, Pavel Stehule 
wrote:

> Hi
>
>
> modern Postgresql has not any problems with special chars
>
>
I think the issue is that he cannot create a proper connection string to
pass to the ODBC driver, since semi-colon is used as a delimiter for the
fields.

could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

[Luca Ferrari]

I've found this strange (to me) behavior when doing nasty things with
indexes and immutable functions:

create table t( pk serial, t text );
insert into t( t ) values( 'hello' ), ('world');
create or replace function f_fake( i int )
returns text
as $body$
declare
  v_t text;
begin
   select t into strict v_t
   from t where pk = i limit 1;
   return v_t;
exception
  when no_data_found then return 'a';
end
$body$
language plpgsql immutable;

Of course, f_fake is not immutable.
When on 10.4 or 11 beta 1 I try to create an index on this nasty
crappy function:

create index idx_fake on t ( f_fake( pk ) );

ERROR:  could not read block 0 in file "base/16392/16444": read only 0
of 8192 bytes
CONTEXT:  SQL statement "select tfrom t where pk =
i limit 1"
PL/pgSQL function f_fake(integer) line 5 at SQL statement

that is somehow correct (because the function cannot be used to build
an index), but then it goes worst:

elect * from t;
ERROR:  could not open relation with OID 16444

If I then disconnect and reconnect I'm able to issue the select and
get back the results. But if I issue a reindex I got the same error
and the table "becames unreadable" for the whole session.
On 10.3 the table is never locked for the session, that is I can
create the index, I can query the table and get the results, but I
cannot reindex. However, even after a reindex, it does allow me to
select data from the table.

So my question is: why this behavior in later PostgreSQL?

Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data

[Moreno Andreo]

Il 22/06/2018 19:56, Adrian Klaver ha scritto:

On 06/22/2018 09:50 AM, Moreno Andreo wrote:

Il 22/06/2018 15:18, Adrian Klaver ha scritto:


Are you sure that the entries where not encrypted with a different 
key because I can't replicate.(More comments below):

(other replies below, inline)
I'm almost sure (you're never absolutely sure :-) ), since I kept all 
commands I entered in PgAdminIII SQL Window, and they're reported above.
On the other side, I tried the same procedure on another field and it 
succeeded.


The only difference between the 2 fields, and I don't know if it can 
make any sense, is that the field I tried now and succeeded was 
created as text, while the other field (dateofbirth) was a timestamp 
I ALTERed with the statement
alter table tbl_p alter column dateofbirth type text using 
to_char(dateofbirth, '-MM-DD');


Assuming the ALTER TABLE was done and then the values where encrypted, 
that does not seem to affect anything here(More below):


test=# create table pgp_alter_test(id integer, birthdate date);
CREATE TABLE
test=# \d pgp_alter_test
    Table "public.pgp_alter_test"
  Column   |  Type   | Collation | Nullable | Default
---+-+---+--+-
 id    | integer |   |  |
 birthdate | date    |   |  |

test=# insert into pgp_alter_test values (1, '2018-06-21');
INSERT 0 1
test=# select * from pgp_alter_test ;
 id | birthdate
+
  1 | 2018-06-21
(1 row)

test=# alter table pgp_alter_test alter column birthdate type text 
using to_char(birthdate, '-MM-DD');

ALTER TABLE

test=# \d pgp_alter_test
    Table "public.pgp_alter_test"
  Column   |  Type   | Collation | Nullable | Default
---+-+---+--+-
 id    | integer |   |  |
 birthdate | text    |

test=# select * from pgp_alter_test ;
 id | birthdate

+

  1 | 2018-06-21

(1 row)




test=# update pgp_alter_test set birthdate = 
pgp_sym_encrypt(birthdate, 'AES_KEY') where id = 1;

UPDATE 1

test=# select * from pgp_alter_test ;
 id |   birthdate
+ 

  1 | 
\xc30d04070302b3f55c80f9ab657c68d23b010cd9d4d82631f89c786394a8bceb35529db07c708e5a0c4f04cf91aed24b5ff397dd99c678ec4f5bb769e148cfae3cdfc5453daaeb34ddd2737c 


(1 row)





 ^

test=# select * from pgp_alter_test where 
pgp_sym_decrypt(birthdate::bytea, 'AES_KEY') = '2018-06-21';

 id |   birthdate
+ 

  1 | 
\xc30d04070302b3f55c80f9ab657c68d23b010cd9d4d82631f89c786394a8bceb35529db07c708e5a0c4f04cf91aed24b5ff397dd99c678ec4f5bb769e148cfae3cdfc5453daaeb34ddd2737c 


(1 row)

I am at a loss now. The only thing I can think of is that data itself 
is actually corrupted. Maybe some sort of language encoding/collation 
issue. Just not sure how to test that at the moment.


Actually, I tried it in a bunch of other fields with varying data types 
and everything went fine.
I don't know if it's as you say and I mismatched keys (and I need 
another pair of glasses) or something else. Just hoping (but being 
confident) it won't happen again.


Now trying to speed up a little some queries involving SELECTing among 
these encrypted fields, if I'm stuck I'll open a new thread.


Thanks,
Moreno.-






I'm just afraid it can happen in production



create table pgp_test(id integer, fld_1 varchar);

insert  into pgp_test values (1, pgp_sym_encrypt('2018-06-21', 
'AES_KEY'))




Have you looked at the entry in its encrypted state to see if it 
looks the same as pgp_sym_encrypt('2018-06-21', 'AES_KEY')?

Yes, it seems to have the same value


So

Re: We find few queries running three times simultaneously with same parameters on postgres db

[amandeep singh]

Get Outlook for Android



From: amandeep singh
Sent: Wednesday, 27 June, 8:42 PM
Subject: Re: We find few queries running three times simultaneously with same 
parameters on postgres db
To: pgsql-general@lists.postgresql.org, Edson Carlos Ericksson Richter


Thanks Edson for ur support.

As I am not a developer , I will share this test case with development team.

Once I get feedback from team, will share it on this mailing list.

Get Outlook for Android

From: Edson Carlos Ericksson Richter 
Sent: Wednesday, June 27, 2018 2:02:19 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: We find few queries running three times simultaneously with same 
parameters on postgres db

Em 26/06/2018 14:26, amandeep singh escreveu:
> Hi Andreas
>
> The value for $1 is same in all queries.
>
> @Edson: number of such running queries are always  3.

I see. It seems a programming logic error to me.
I also use JPA (Hibernate and/or EclipseLink) and I don't have such problem.
But I could replicate your scenario doing the following (with
EclipseLink, didn't test with Hibernate):

1) In persistence.xml, disable all caches (this is very important for
make reproducible)
2) Load three Person records that live on same city (objects Person and
City mapped to tables Person and City, being city an attribute of Person
object):

TypedQuery qry = em.createQuery("select P from Person P where
P.city.name = :cityname");
qry.setParameter("cityname", "Porto Alegre");
qry.setMaxResults(3);
List rs = qry.getResultList();

3) This will cause one query against Person table, and exactly 3 queries
issued with same parameters to City table.

I really don't know how your code works, and is quite hard to guess, but
as you can see, it is easy to reproduce similar behavior.
Now, if I enable EclipseLink caches, only one query is issued for each
City key - so, I'll have one query for Person and one query for  only.

Hope this helps to enlighten your problem.

:-)

Regards,

Edson

Re: We find few queries running three times simultaneously with same parameters on postgres db

[amandeep singh]

Thanks Edson for ur support.

As I am not a developer , I will share this test case with development team.

Once I get feedback from team, will share it on this mailing list.

Get Outlook for Android

From: Edson Carlos Ericksson Richter 
Sent: Wednesday, June 27, 2018 2:02:19 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: We find few queries running three times simultaneously with same 
parameters on postgres db

Em 26/06/2018 14:26, amandeep singh escreveu:
> Hi Andreas
>
> The value for $1 is same in all queries.
>
> @Edson: number of such running queries are always  3.

I see. It seems a programming logic error to me.
I also use JPA (Hibernate and/or EclipseLink) and I don't have such problem.
But I could replicate your scenario doing the following (with
EclipseLink, didn't test with Hibernate):

1) In persistence.xml, disable all caches (this is very important for
make reproducible)
2) Load three Person records that live on same city (objects Person and
City mapped to tables Person and City, being city an attribute of Person
object):

TypedQuery qry = em.createQuery("select P from Person P where
P.city.name = :cityname");
qry.setParameter("cityname", "Porto Alegre");
qry.setMaxResults(3);
List rs = qry.getResultList();

3) This will cause one query against Person table, and exactly 3 queries
issued with same parameters to City table.

I really don't know how your code works, and is quite hard to guess, but
as you can see, it is easy to reproduce similar behavior.
Now, if I enable EclipseLink caches, only one query is issued for each
City key - so, I'll have one query for Person and one query for  only.

Hope this helps to enlighten your problem.

:-)

Regards,

Edson

Re: EXTERNAL: Re: Database name with semicolon

[Pavel Stehule]

Hi

2018-06-27 16:39 GMT+02:00 Joby John :

> Hi Pavel,
>
>
>
> Thanks for coming back to me.
>

please, don't top post
https://en.wikipedia.org/wiki/Posting_style#Top-posting


>
> The version of the Postgres server where I am trying is: 9.5.3
>

9.5 is modern Postgres. There should not be problems with names.



> Yes, I tried putting the database name in double quotes with no luck.
>
> Please note we are using PostgreSQL Unicode ODBC driver (version 10.2) to
> communicate with the server.
>

looks like ODBC issue - semicolon has some semantic there

https://stackoverflow.com/questions/22398212/escape-semicolon-in-odbc-connection-string-in-app-config-file

but probably you read these pages

Maybe some combination of quotes is necessary

https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms722656(v=vs.85)

you can try

dbname = '"; sss"'




>
> Regards,
>
> Joby John
>
>
>
> *From:* Pavel Stehule [mailto:pavel.steh...@gmail.com]
> *Sent:* 27 June 2018 15:26
> *To:* Joby John 
> *Cc:* pgsql-gene...@postgresql.org
> *Subject:* EXTERNAL: Re: Database name with semicolon
>
>
>
> Hi
>
>
>
> 2018-06-27 15:22 GMT+02:00 Joby John :
>
> Hi,
>
>
>
> We have a windows application which communicate to Postgres database via
> PostgreSQL ODBC drivers for windows.
>
> One of our customers has a database with semicolon in its name (e.g.: “db;
> name”) and our application is failing to establish connection to that
> database saying like it could not find the database.
>
> We tried all known possibilities by enclosing the DB name under quotes,
> curly brackets etc. but none solved the problem.
>
>
>
> I wonder whether there is any possible way to achieve connection to such
> database. If not, probably it’s better not to support semicolons within the
> database name.
>
> Not sure whether I need to submit this query as a bug or not. Any help is
> much appreciated.
>
>
>
> modern Postgresql has not any problems with special chars
>
>
>
> postgres=# create database "bad name ";
> CREATE DATABASE
> postgres=# \q
> [pavel@nemesis ~]$ psql 'bad name '
> psql (11beta1)
> Type "help" for help.
>
> bad name =#
>
>
>
> What is version of Postgres?
>
>
>
> Did you try double quotes?
>
>
>
> Regards
>
>
>
> Pavel
>
>
>
>
>
>
>
> Thanks and Regards,
>
> Joby John
> --
>
> Joby John
> Software Developer
> NCC Group
> Kings Court, Kingston Road, Leatherhead, KT22 7SL
>
> Telephone: +44 1372 383 800 <+44%201372%20383%20800>
> Mobile:
> Website: www.nccgroup.trust
> Twitter: @NCCGroupplc 
>
> 
> --
>
> This email is sent for and on behalf of NCC Group. NCC Group is the
> trading name of NCC Services Limited (Registered in England CRN: 2802141).
> The ultimate holding company is NCC Group plc (Registered in England CRN:
> 4627044). This email may be confidential and/or legally privileged.
>
>
> --
> Joby John
> Software Developer
> NCC Group
> Kings Court, Kingston Road, Leatherhead, KT22 7SL
>
> Telephone: +44 1372 383 800 <+44%201372%20383%20800>
> Mobile:
> Website: www.nccgroup.trust
> Twitter: @NCCGroupplc 
> 
> --
>
> This email is sent for and on behalf of NCC Group. NCC Group is the
> trading name of NCC Services Limited (Registered in England CRN: 2802141).
> The ultimate holding company is NCC Group plc (Registered in England CRN:
> 4627044). This email may be confidential and/or legally privileged.
>

Re: Database name with semicolon

[Pavel Stehule]

Hi

2018-06-27 15:22 GMT+02:00 Joby John :

> Hi,
>
>
>
> We have a windows application which communicate to Postgres database via
> PostgreSQL ODBC drivers for windows.
>
> One of our customers has a database with semicolon in its name (e.g.: “db;
> name”) and our application is failing to establish connection to that
> database saying like it could not find the database.
>
> We tried all known possibilities by enclosing the DB name under quotes,
> curly brackets etc. but none solved the problem.
>
>
>
> I wonder whether there is any possible way to achieve connection to such
> database. If not, probably it’s better not to support semicolons within the
> database name.
>
> Not sure whether I need to submit this query as a bug or not. Any help is
> much appreciated.
>

modern Postgresql has not any problems with special chars

postgres=# create database "bad name ";
CREATE DATABASE
postgres=# \q
[pavel@nemesis ~]$ psql 'bad name '
psql (11beta1)
Type "help" for help.

bad name =#

What is version of Postgres?

Did you try double quotes?

Regards

Pavel



>
>
> Thanks and Regards,
>
> Joby John
> --
> Joby John
> Software Developer
> NCC Group
> Kings Court, Kingston Road, Leatherhead, KT22 7SL
>
> Telephone: +44 1372 383 800 <+44%201372%20383%20800>
> Mobile:
> Website: www.nccgroup.trust
> Twitter: @NCCGroupplc 
> 
> --
>
> This email is sent for and on behalf of NCC Group. NCC Group is the
> trading name of NCC Services Limited (Registered in England CRN: 2802141).
> The ultimate holding company is NCC Group plc (Registered in England CRN:
> 4627044). This email may be confidential and/or legally privileged.
>

Database name with semicolon

[Joby John]

Hi,

We have a windows application which communicate to Postgres database via 
PostgreSQL ODBC drivers for windows.
One of our customers has a database with semicolon in its name (e.g.: "db; 
name") and our application is failing to establish connection to that database 
saying like it could not find the database.
We tried all known possibilities by enclosing the DB name under quotes, curly 
brackets etc. but none solved the problem.

I wonder whether there is any possible way to achieve connection to such 
database. If not, probably it's better not to support semicolons within the 
database name.
Not sure whether I need to submit this query as a bug or not. Any help is much 
appreciated.

Thanks and Regards,
Joby John

Joby John
Software Developer
NCC Group
Kings Court, Kingston Road, Leatherhead, KT22 7SL

Telephone: +44 1372 383 800
Mobile: 
Website: www.nccgroup.trust
Twitter: @NCCGroupplc
[https://www.nccgroup.trust/static/img/emaillogo/ncc-group-logo.png]  



This email is sent for and on behalf of NCC Group. NCC Group is the trading 
name of NCC Services Limited (Registered in England CRN: 2802141). The ultimate 
holding company is NCC Group plc (Registered in England CRN: 4627044). This 
email may be confidential and/or legally privileged.

Re: We find few queries running three times simultaneously with same parameters on postgres db

[Edson Carlos Ericksson Richter]

Em 26/06/2018 14:26, amandeep singh escreveu:

Hi Andreas

The value for $1 is same in all queries.

@Edson: number of such running queries are always  3.


I see. It seems a programming logic error to me.
I also use JPA (Hibernate and/or EclipseLink) and I don't have such problem.
But I could replicate your scenario doing the following (with 
EclipseLink, didn't test with Hibernate):


1) In persistence.xml, disable all caches (this is very important for 
make reproducible)
2) Load three Person records that live on same city (objects Person and 
City mapped to tables Person and City, being city an attribute of Person 
object):


TypedQuery qry = em.createQuery("select P from Person P where 
P.city.name = :cityname");

qry.setParameter("cityname", "Porto Alegre");
qry.setMaxResults(3);
List rs = qry.getResultList();

3) This will cause one query against Person table, and exactly 3 queries 
issued with same parameters to City table.


I really don't know how your code works, and is quite hard to guess, but 
as you can see, it is easy to reproduce similar behavior.
Now, if I enable EclipseLink caches, only one query is issued for each 
City key - so, I'll have one query for Person and one query for  only.


Hope this helps to enlighten your problem.

:-)

Regards,

Edson

Re: Example setup for Odyssey connection pooler?

[Adrian Klaver]

On 06/27/2018 06:07 AM, Pablo Hendrickx wrote:

Please reply to list also.
Ccing list.


Yes that's the config file. How do I start Odyssey as a service?


There are some example scripts:

https://github.com/yandex/odyssey/tree/master/scripts

If this does not help you might want to contact the folks at Yandex at 
the email listed here:


https://github.com/yandex

opensou...@yandex-team.ru



On 27-06-18 14:53, Adrian Klaver wrote:

On 06/27/2018 04:37 AM, Pablo Hendrickx wrote:

Hi Postgres!

The installation instructions for Odyssey are very clear and helpful, 
but there are no setup instructions to be found for this 
multithreading tool.

Can someone provide a simple example setup?


?:







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

Re: Example setup for Odyssey connection pooler?

[Adrian Klaver]

On 06/27/2018 04:37 AM, Pablo Hendrickx wrote:

Hi Postgres!

The installation instructions for Odyssey are very clear and helpful, 
but there are no setup instructions to be found for this multithreading 
tool.

Can someone provide a simple example setup?


?:
https://github.com/yandex/odyssey/blob/master/odyssey.conf



Met vriendelijke groeten,

Pablo Hendrickx
Open Source DBA

+32 489 73 09 37
pablo.hendri...@exitas.be 

Quality. Passion. Personality

www.exitas.be  | Veldkant 31 | 2550 Kontich





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

Re: Fwd: problem with using VBA connection string to postgresql

[Łukasz Jarych]

Hi Adrian,

John Pedro answered me,

problem was that i had ODBC in the beginning of connection string...
Strange, but in table connector you have to have ODBC in connection string,
in directly connetion you can not have...

Best

2018-06-27 14:49 GMT+02:00 Adrian Klaver :

> On 06/27/2018 05:27 AM, Łukasz Jarych wrote:
>
>> Maybe some security here?
>>
>
> 32/64 bit mismatch?
>
> Do you have both the 32 and 64 bit versions of the ODBC driver installed?
>
> Is your application calling the correct version?
>
>
>> Best,
>> Jacek
>>
>>
>> Hi Guys,
>>
>> I am using code :
>>
>> Sub copyingSchemas()
>> Dim dbCon As Object
>> Dim rst As Object
>> Dim username, pass, strConnect As String
>> username = Environ("username")
>> pass = "PasswordAccess"
>> Set dbCon = CreateObject("ADODB.Connection")
>> Set rst = CreateObject("ADODB.Recordset")
>> '''You can use a pass-through query to list the table names from
>> your SQL Server database. Open a recordset based on that query. Then
>> loop through the recordset rows and link each table.
>> username = "postgres"
>> pass = "1234"
>> strConnect = "ODBC;DRIVER={PostgreSQL Unicode};" & _
>> "DATABASE=AccessLog;" & _
>> "SERVER=localhost;" & _
>> "PORT=5432;" & _
>> "UID=" & username & ";" & _
>> "PWD=" & pass & ";" & _
>> "Trusted_Connection=Yes;"
>> dbCon.ConnectionString = strConnect
>> dbCon.Open
>> Dim strSQl As String
>> strSQl = "SELECT * FROM t_cpuinfo();"
>> rst.Open strSQl
>> dbCon.Close
>>
>> End Sub
>>
>>
>> error is :
>>
>>
>>
>>
>> Linked Table connection string which is working :
>>
>>
>>
>> Why?
>>
>> Please help,
>> Best,
>> Jacek
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

problem with using VBA connection string to postgresql

[Łukasz Jarych]

Hi Guys,

I am using code :

Sub copyingSchemas()
> Dim dbCon As Object
> Dim rst As Object
> Dim username, pass, strConnect As String
> username = Environ("username")
> pass = "PasswordAccess"
> Set dbCon = CreateObject("ADODB.Connection")
> Set rst = CreateObject("ADODB.Recordset")
> '''You can use a pass-through query to list the table names from your SQL
> Server database. Open a recordset based on that query. Then loop through
> the recordset rows and link each table.
> username = "postgres"
> pass = "1234"
> strConnect = "ODBC;DRIVER={PostgreSQL Unicode};" & _
> "DATABASE=AccessLog;" & _
> "SERVER=localhost;" & _
> "PORT=5432;" & _
> "UID=" & username & ";" & _
> "PWD=" & pass & ";" & _
> "Trusted_Connection=Yes;"
> dbCon.ConnectionString = strConnect
> dbCon.Open
> Dim strSQl As String
> strSQl = "SELECT * FROM t_cpuinfo();"
> rst.Open strSQl
> dbCon.Close
>
> End Sub


error is :




Linked Table connection string which is working :



Why?

Please help,
Best,
Jacek

Example setup for Odyssey connection pooler?

[Pablo Hendrickx]

Hi Postgres!

The installation instructions for Odyssey are very clear and helpful, 
but there are no setup instructions to be found for this multithreading 
tool.

Can someone provide a simple example setup?

Met vriendelijke groeten,

Pablo Hendrickx
Open Source DBA

+32 489 73 09 37
pablo.hendri...@exitas.be 

Quality. Passion. Personality

www.exitas.be  | Veldkant 31 | 2550 Kontich

Re: Code of Conduct committee: call for volunteers

[Raymond O'Donnell]

On 27/06/18 10:44, ERR ORR wrote:

I am apparently late in this discussion.
I COMPLETELY DISAGREE with the idea of having a CoC in Postgres.
I've participated for many years in the lists and don't remember one 
instance of inappropriate behavior.

A CoC will only contribute to decrease the quality of the Postgres code.
How? It will entrench women and fags in the Postgres community.


Is someone running unit tests on the CoC? :-)

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

Re: Code of Conduct committee: call for volunteers

[ERR ORR]

I am apparently late in this discussion.
I COMPLETELY DISAGREE with the idea of having a CoC in Postgres.
I've participated for many years in the lists and don't remember one
instance of inappropriate behavior.
A CoC will only contribute to decrease the quality of the Postgres code.
How? It will entrench women and fags in the Postgres community.
Having worked many years with women, I can say from experience that most of
them are incompetent AF.
Fags are that plus disgusting.
I hope that you'll consider the CoC well.
It is an enmitous takeover.

Tom Lane  schrieb am Do., 7. Juni 2018, 22:58:

> The proposed Postgres Code of Conduct [1] calls for an investigation
> and enforcement committee, which is to be appointed by and ultimately
> answerable to the core team, though no core members may sit on it.
>
> The core team is pleased to announce that Stacey Haysler has agreed
> to serve as the first Chair of the CoC committee.  Many of you know
> Stacey from various conferences etc, and I don't feel that I need to
> repeat her excellent qualifications for this task.
>
> While we have a few names in mind for additional committee members,
> we need more in order that the committee can represent a diverse
> range of viewpoints and cultures.  Therefore we are calling for
> volunteers, as indeed is required by the proposed CoC.  This call
> will remain open until June 15; the committee membership will be
> decided and announced before the end of June.
>
> We encourage community members who, perhaps, have not previously
> taken large roles in the community to step forward for this.
> What is needed is not technical skills but empathy and the ability
> to understand varying viewpoints.
>
> If you are interested in serving, please write to c...@postgresql.org
> (do not follow up to this message).  Please include answers to these
> questions:
>
> 1. What interests you about being on the CoC Committee?
>
> 2. Have you been on another CoC Committee, or had a similar role at
> another organization?  (Prior experience is not required, it's just
> helpful to know everyone's background.)
>
> 3. What else would you like to tell us that is helpful for us to know
> about your potential involvement with the CoC Committee?
>
>
> regards, tom lane
>
> [1] https://wiki.postgresql.org/wiki/Code_of_Conduct
>
>

Sv: Re: Sv: Re: CTE optimization fence

[Andreas Joseph Krogh]

På onsdag 27. juni 2018 kl. 11:44:05, skrev Adrien NAYRAT <
adrien.nay...@anayrat.info >:
On 06/27/2018 09:58 AM, Andreas Joseph Krogh wrote:
 >      >
 >      > but we have to settle on a way of controlling it.
 >
 >     +1 from me.
 >
 >     I am running more and more into situations where people consider
 >     this a bug rather than a feature.
 >
 >     FWIW, I think a GUC that switches between the current (mostly
 >     unwanted, at least surprising)
 >     way and one where the CTE is optimized together with the main query
 >     would suit "most" people.
 >
 >     For sake of compatibility this could default to the current behaviour
 >
 > +1 from me. The default should be "no fence" for sake of least surprise
 > I think. Documenting the change would be sufficient.
 > I hope this will be picked up in the comming V12-cycle.


 FYI this subject has been discussed in this thread :
 https://www.postgresql.org/message-id/5351711493487900%40web53g.yandex.ru

 Regards,
  
 
I know. I hate the INLINE proposal and hope default-behaviour will be like in 
other DBs, inline like sub-query as default. GUC for preserving fence is what I 
hope will happen.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: pg_upgrade and wraparound

[Daniel Verite]

Alexander Shutyaev wrote:

> Is there any hope the issue with pg_upgrade can be resolved? If not,
> could you give me some hints as to how can I decrease time needed
> for pg_dumpall | psql?

Not sure about the larger problem, but for the part about having more
than 1 million large objects that get committed individually, setting
fsync=off for the reload phase is likely to help a lot.

Just don't forget to turn it on again when it's done. See
https://blog.2ndquadrant.com/postgresql-fsync-off-warning-in-config-file/


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

Re: Sv: Re: CTE optimization fence

[Adrien NAYRAT]

On 06/27/2018 09:58 AM, Andreas Joseph Krogh wrote:

 >
 > but we have to settle on a way of controlling it.

+1 from me.

I am running more and more into situations where people consider
this a bug rather than a feature.

FWIW, I think a GUC that switches between the current (mostly
unwanted, at least surprising)
way and one where the CTE is optimized together with the main query
would suit "most" people.

For sake of compatibility this could default to the current behaviour

+1 from me. The default should be "no fence" for sake of least surprise 
I think. Documenting the change would be sufficient.

I hope this will be picked up in the comming V12-cycle.



FYI this subject has been discussed in this thread : 
https://www.postgresql.org/message-id/5351711493487900%40web53g.yandex.ru


Regards,

Re: Too many range table entries error

[Akshaya Acharya]

Thank you very much for your guidance on this.

I was speaking with a friend about this, and he said something to the
effect of "keep it aside and do it later" and then a solution hit me.

Since we can allow this data to be stale in our case, replacing some key
views in the hierarchy of views with materialized views has worked for us.

With regards to the architecture/design, I am still learning as I go along,
I will articulate my thoughts and post later. In the long term, we will
redesign the system with this new knowledge in mind.

Regards
Akshaya

On Tue, 26 Jun 2018 at 06:08, Tom Lane  wrote:

> Andres Freund  writes:
> > On 2018-06-25 13:46:06 +0530, Akshaya Acharya wrote:
> >> Our entire application-all our business logic-is built as layers of
> views
> >> inside the database. The ref counts sort of multiple at each layer,
> hence
> >> the large number.
>
> > That still doesn't explain how you realistically get to 40k references,
> > and how that's a reasonable design.
>
> The short answer here is that even if the system accepted queries with
> that many tables, it's really unlikely to perform acceptably --- in fact,
> I'm a bit astonished that you even found a way to reach this error without
> having waited a few hours beforehand.  And we are *not* going to promise
> to fix all the performance issues you will hit with a schema design like
> this.  Redesign.  Please.
>
> regards, tom lane
>

Sv: Re: CTE optimization fence

[Andreas Joseph Krogh]

På onsdag 27. juni 2018 kl. 07:45:25, skrev Thomas Kellerer mailto:spam_ea...@gmx.net>>:
Tom Lane schrieb am 27.06.2018 um 05:48:
 >> I see there was some discussion last year about removing the CTE
 >> optimization fence (e.g.
 >> http://www.postgresql-archive.org/CTE-inlining-td5958992.html) but can't
 >> find anything more recent. Does anyone know if this is still under
 >> consideration?
 >
 > but we have to settle on a way of controlling it.

 +1 from me.

 I am running more and more into situations where people consider this a bug 
rather than a feature.

 FWIW, I think a GUC that switches between the current (mostly unwanted, at 
least surprising)
 way and one where the CTE is optimized together with the main query would 
suit "most" people.

 For sake of compatibility this could default to the current behaviour
 
+1 from me. The default should be "no fence" for sake of least surprise I 
think. Documenting the change would be sufficient.
I hope this will be picked up in the comming V12-cycle.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: About "Cost-based Vacuum Delay"

[Laurenz Albe]

Ilyeop Yi wrote:
> Currently, I am working with a workload that is mostly insert and update, and 
> its performance suffers from autovacuum.
> 
> I've adjusted parameters such as vacuum_cost_delay and vacuum_cost_limit, but 
> they have no significant effect.
> 
> So, I would like to find a way to pause a running vacuum during bursty 
> insert/update period and resume the vacuum after that period.
> 
> Is there such a way?

Please keep the list copied.

You can do

   ALTER TABLE mytab SET (autovacuum_enabled = off);

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com