Re: List all columns referencing an FK

[Peter Eisentraut]

On 2/8/18 05:31, Andreas Joseph Krogh wrote:
> Back in 2008 I asked this
> question: 
> http://www.postgresql-archive.org/Finding-all-tables-that-have-foreign-keys-referencing-a-table-td2153236.html

> I wonder, is this now possible using information_schema only, or are
> there still pieces missing in the standard holding this back?

I think you'll still have the same problems if the same constraint name
appears more than once per schema.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: algo for canceling a deadlocked transaction

[Christophe Pettus]

> On Apr 9, 2018, at 07:33, Thomas Poty  wrote:
> 
> ok, and long  answer ? is it random?

It's not literally random, but from the application point of view, it's not 
predictable.  For example, it's not always the one that opened first, or any 
other consistent measure.
--
-- Christophe Pettus
   x...@thebuild.com

Re: best way to write large data-streams quickly?

[Steve Atkins]

> On Apr 9, 2018, at 8:49 AM, Mark Moellering  
> wrote:
> 
> Everyone,
> 
> We are trying to architect a new system, which will have to take several 
> large datastreams (total of ~200,000 parsed files per second) and place them 
> in a database.  I am trying to figure out the best way to import that sort of 
> data into Postgres.  
> 
> I keep thinking i can't be the first to have this problem and there are 
> common solutions but I can't find any.  Does anyone know of some sort method, 
> third party program, etc, that can accept data from a number of different 
> sources, and push it into Postgres as fast as possible?

Take a look at http://ossc-db.github.io/pg_bulkload/index.html. Check the 
benchmarks for different situations compared to COPY.

Depending on what you're doing using custom code to parse your data and then do 
multiple binary COPYs in parallel may be better.

Cheers,
  Steve

Re: algo for canceling a deadlocked transaction

[Tom Lane]

Christophe Pettus  writes:
>> On Apr 9, 2018, at 07:33, Thomas Poty  wrote:
>> ok, and long  answer ? is it random?

> It's not literally random, but from the application point of view, it's not 
> predictable.  For example, it's not always the one that opened first, or any 
> other consistent measure.

It's whichever one runs the deadlock detector first after the circular
wait becomes established.  For instance:

* Process A takes lock L1

* Process B takes lock L2

* Process A tries to take lock L2, blocks

* Process B tries to take lock L1, blocks (now a deadlock exists)

Process A will run the deadlock detector one deadlock_timeout after
blocking.  If that happens before B has blocked, then A will see
no deadlock and will go back to waiting.  In that case, when B's
own deadlock_timeout expires and it runs the deadlock detector,
it will see the deadlock and fix it by canceling its own wait.
On the other hand, if B started to wait less than one deadlock_timeout
after A did, then A will be first to observe the deadlock and it will
cancel itself, not B.

So you can't predict it unless you have a lot of knowledge about
the timing of events.  You could probably make it more predictable
by making deadlock_timeout either very short or very long, but
neither of those are desirable things to do.

regards, tom lane

best way to write large data-streams quickly?

[Mark Moellering]

Everyone,

We are trying to architect a new system, which will have to take several
large datastreams (total of ~200,000 parsed files per second) and place
them in a database.  I am trying to figure out the best way to import that
sort of data into Postgres.

I keep thinking i can't be the first to have this problem and there are
common solutions but I can't find any.  Does anyone know of some sort
method, third party program, etc, that can accept data from a number of
different sources, and push it into Postgres as fast as possible?

Thanks in advance,

Mark Moellering

Re: How to monitor logical replication initial sync?

[Peter Eisentraut]

On 3/15/18 09:19, bricklen wrote:
> How does one monitor the status or progress of an initial sync under
> logical replication?  For example:
> 
> * I create a publication in database db_pub
> * I create a subscription in database db_sub
> * In 15 minutes I want to check an see that the initial sync is N%
> complete
> 
> Is it possible to tell when the initial sync is complete, or better
> yet, how complete it is?
> 
> 
> ​This is a question I'm quite interested in as well (and one I do not
> have an answer to).​
>  
> ​Does anyone with more familiarity ​with logical replication have any
> suggestions on how to determine the status of the initial sync?

Something like

select * from pg_subscription_rel where srsubstate <> 'r' and srsubid =
(select oid from pg_subscription where subname = 'mysub');

The key is checking the srsubstate column for 'r' (ready).

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Conflict between JSON_AGG and COPY

[Adrian Klaver]

On 04/08/2018 08:44 PM, Đỗ Ngọc Trí Cường wrote:

Dear Arian Klaver,

I think there is a misunderstood here.

I think that I quite understand how is the second query run.

The question I asked here is why exporting data, which is generated by a 
query "JSON_AGG" , with command "COPY". The data contain "\n" as 2 
characters instead of "new line" character.


The second query is for the old version of PostgreSQL (9.3 and previous) 
cause of they don't have JSON_AGG aggregate function. Since 9.4, we have 
"JSON_AGG" already. So I want to rewrite and reduce the length of the 
query. But it is don't work as I expected with command COPY.


COPY
(select json_build_object('RECORDS', ARRAY_AGG(t) ) from test_table t) 
TO '/home/postgres/test2.json';


cat test2.json

{"RECORDS" : 
[{"id":1,"username":"john","fullname":"John"},{"id":2,"username":"anna","fullname":"Anna"},{"id":3,"username":"sussi","fullname":"Sussi"},{"id":4,"username":"david","fullname":"David 
Beckham"},{"id":5,"username":"wayne","fullname":"Wayne Rooney"}]}





Thank you and best regards,


Đỗ Ngọc Trí*Cường*(Mr.) | *Software Development Department*| +84 28 3715 
5325



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

Re: Conflict between JSON_AGG and COPY

[Daniel Verite]

   Đỗ Ngọc Trí Cường wrote:

> I want to export it to a file in JSON format so I run the query as below: 
> COPY (SELECT...) TO '/home/postgres/test1.json'

COPY TO applies its own format rules on top of the data, which include
among other things, quoting the newline characters. What you seem
to expect is to copy a single-row, single-column piece of data verbatim
to the output file, but COPY does not offer that.
Such an option has already been discussed among developers, for
instance in this thread as a 'RAW' format:
https://www.postgresql.org/message-id/CAFj8pRAfer%2Bip3JCMTnvzra2QK7W9hk0hw1YqE8%2B%2BPZA1OqSJw%40mail.gmail.com
but eventually it was not implemented.

The unformatted output can be obtained client-side without COPY:
psql -Atc "SELECT row_to_json(...)"  > /path/to/file

If you really need it server-side, a function that can write a result to
a file is required. I don't think it exists as such out of the box, but
you may either:

-  write your own function in any untrusted language to
do just that (open file, write the piece of text into it, close it).

- or see pg_file_write() from the adminpack contrib module
https://www.postgresql.org/docs/current/static/adminpack.html
With that, you may call, as a superuser:
select pg_file_write('path/to/file.json', 
  (select row_to_json(...))::text,
  false);
But note that the path must be relative to the PGDATA directory.

- or export the data as an intermediary large object at the cost of some
data churn in the large objects. And in that case, the path is not
constrained to postgres data directory.

do $$
declare
 id oid;
 j json;
begin
 j := (select row_to_json(t) from );
 id := lo_from_bytea(0, convert_to(j::text, 'UTF-8'));
 perform lo_export(id, '/path/to/file.json');
 perform lo_unlink(id);
end;
$$ language plpgsql;

For all these server-side methods, you need to be superuser, just like for
COPY TO file anyway.

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

Carto ODBC_FDW issue "ERROR: Connecting to driver" from PG to DB2

[karthik kumar]

Hi Guru's,

I am trying to access few table present in DB2 LUW from postgres database.
All commands work fine, however when I try to select data from table it
throws error:

pg@a92a3741d40e:~/odbc_fdw$ psql -d postgres
psql (10.1)
Type "help" for help.

postgres=# create extension odbc_fdw;
CREATE EXTENSION

postgres=# CREATE SERVER odbc_db2 FOREIGN DATA WRAPPER odbc_fdw OPTIONS
(dsn 'SAMPLE');
CREATE SERVER



postgres=# CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (odbc_UID
'db2inst1', odbc_PWD 'db2inst1');
CREATE USER MAPPING

postgres=#
postgres=# CREATE FOREIGN TABLE
postgres-#   odbc_testt (
postgres(# id integer
postgres(#   )
postgres-#   SERVER odbc_db2
postgres-#   OPTIONS (
postgres(# odbc_database 'SAMPLE',
postgres(# odbc_schema 'db2inst1',
postgres(# sql_query 'select x from `db2inst1`.`TESTT`',
postgres(# sql_count 'select count(id) from `db2inst1`.`dblist`'
postgres(#   );
CREATE FOREIGN TABLE

postgres=# select * from odbc_testt;
ERROR:  Connecting to driver



pg@a92a3741d40e:~/odbc_fdw$ isql -v SAMPLE db2inst1 db2inst1
+---+
| Connected!|
|   |
| sql-statement |
| help [tablename]  |
| quit  |
|   |
+---+
SQL> select * from testt;
++
| X  |
++
| 1  |
++
SQLRowCount returns -1
1 rows fetched
SQL> quit


root@a92a3741d40e:/home/pg# cat /etc/odbc.ini
[SAMPLE]
Description = DB2 remote SAMPLE Database
Driver=/home/db2inst1/sqllib/lib64/libdb2.so
SERVERNAME=MYDB2
UID=db2inst1
PWD=db2inst1
port=5


Thanks,
Karthik.

Fwd: Carto ODBC_FDW issue "ERROR: Connecting to driver" from PG to DB2

[karthik kumar]

On 04/09/2018 01:01 PM, karthik kumar wrote:

> Hi Guru's,
>
> I am trying to access few table present in DB2 LUW from postgres database.
> All commands work fine, however when I try to select data from table it
> throws error:
>
> pg@a92a3741d40e:~/odbc_fdw$ psql -d postgres
> psql (10.1)
> Type "help" for help.
>
> postgres=# create extension odbc_fdw;
> CREATE EXTENSION
>
> postgres=# CREATE SERVER odbc_db2 FOREIGN DATA WRAPPER odbc_fdw OPTIONS
> (dsn 'SAMPLE');
> CREATE SERVER
>
>
>
> postgres=# CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (odbc_UID
> 'db2inst1', odbc_PWD 'db2inst1');
>

Assuming using this:

https://github.com/ZhengYang/odbc_fdw

I believe that should be username and password per above link:

"CREATE USER MAPPING FOR postgres
SERVER odbc_server
OPTIONS (username 'root', password '');
"

Yes.

CREATE USER MAPPING
>
> postgres=#
> postgres=# CREATE FOREIGN TABLE
> postgres-#   odbc_testt (
> postgres(# id integer
> postgres(#   )
> postgres-#   SERVER odbc_db2
> postgres-#   OPTIONS (
> postgres(# odbc_database 'SAMPLE',
>

Is the above the database name as well as the DSN name?

Yes. It is.

postgres(# odbc_schema 'db2inst1',
> postgres(# sql_query 'select x from `db2inst1`.`TESTT`',
>

Is the column name case sensitive, because below it shows up as X?

No. But i tried both.

I would try without the sql_query and sql_count queries as they are
optional.

I removed both and tried still same error.


postgres(# sql_count 'select count(id) from `db2inst1`.`dblist`'
> postgres(#   );
> CREATE FOREIGN TABLE
>
> postgres=# select * from odbc_testt;
> ERROR:  Connecting to driver
>
>
>
> pg@a92a3741d40e:~/odbc_fdw$ isql -v SAMPLE db2inst1 db2inst1
> +---+
> | Connected!|
> |   |
> | sql-statement |
> | help [tablename]  |
> | quit  |
> |   |
> +---+
> SQL> select * from testt;
> ++
> | X  |
> ++
> | 1  |
> ++
> SQLRowCount returns -1
> 1 rows fetched
> SQL> quit
>
>
> root@a92a3741d40e:/home/pg# cat /etc/odbc.ini
> [SAMPLE]
> Description = DB2 remote SAMPLE Database
> Driver=/home/db2inst1/sqllib/lib64/libdb2.so
> SERVERNAME=MYDB2
> UID=db2inst1
> PWD=db2inst1
> port=5
>
>
> Thanks,
> Karthik.
>

Thanks,
Karthik.

Re: Fwd: Carto ODBC_FDW issue "ERROR: Connecting to driver" from PG to DB2

[Adrian Klaver]

On 04/09/2018 02:10 PM, karthik kumar wrote:





On 04/09/2018 01:01 PM, karthik kumar wrote:

Hi Guru's,

I am trying to access few table present in DB2 LUW from postgres
database.
All commands work fine, however when I try to select data from table
it throws error:

pg@a92a3741d40e:~/odbc_fdw$ psql -d postgres
psql (10.1)
Type "help" for help.

postgres=# create extension odbc_fdw;
CREATE EXTENSION

postgres=# CREATE SERVER odbc_db2 FOREIGN DATA WRAPPER odbc_fdw
OPTIONS (dsn 'SAMPLE');
CREATE SERVER



postgres=# CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS
(odbc_UID  'db2inst1', odbc_PWD 'db2inst1');


Assuming using this:

https://github.com/ZhengYang/odbc_fdw 



I believe that should be username and password per above link:

"CREATE USER MAPPING FOR postgres
         SERVER odbc_server
         OPTIONS (username 'root', password '');
"

Yes.


Does 'yes' mean you changed this:

CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (odbc_UID 
'db2inst1', odbc_PWD 'db2inst1');


to this?:

CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (username 
'db2inst1', password 'db2inst1');




Thanks,
Karthik.




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

Re: ERROR: found multixact from before relminmxid

[Peter Geoghegan]

On Mon, Apr 9, 2018 at 7:01 AM, Tomas Vondra
 wrote:
> The bigger question is whether this can actually detect the issue. If
> it's due to an storage issue, then perhaps yes. But if you only see
> multixact issues consistently and nothing else, it might easily be a
> PostgreSQL bug (in which case the checksum will be correct).

You can also run amcheck. Get the version targeting earlier Postgres
releases off Github (there are packages for most Linux systems). This
can verify that the heap is consistent with indexes.

-- 
Peter Geoghegan

Re: Rationale for aversion to the central database?

[Tim Cross]

Peter J. Holzer  writes:

> In my applications I use SQL heavily. RDBMs are good at processing
> queries, so use them for that. If all you want is a key-value store,
> don't use PostgreSQL. I'm not very fond of ORMs. I know what I want to
> do and can express it in SQL. An ORM makes me translate that into a
> different (and usually inferior) query language, which is then
> translated back into SQL. That doesn't make things easier for me.
>
Could not agree more! My experience has been that ORMs just get in the
way. Worse yet, when I've investigated performance problems raised by
developers, I've often found it is due to the ORM layer, which is unable
to map more complex queries efficiently.

The only 'layer' I've ever used which I liked was HugSQL. I quite liked
this approach as you write the queries in SQL and these get exposed to
the application layer as high level functions, so gives a nice clean interface.

>
> I come from Oracle, not MySQL, But I have also used MySQL, and I guess
> the very wide gap in capabilities between Oracle and MySQL made me
> cautious about putting too much into the database. There is also the
> expectation that you should be able to use a different database engine
> (SQL is a standard, right?) just like you should be able to use a
> different C compiler, but in practice that never works. And of course I
> wasn't very impressed with PL/SQL. (PostgreSQL gives you a much wider
> range of languages for stored procedures than Oracle, but PL/PerlU still
> isn't quite the same as Perl (And I suspect it's the same for Python).
>
> hp

Again, totally agree. Nice in theory and reminds me of the 'write once,
run everywhere' dream. Very few of the places I've worked have actually
maintained cross database functionality for long, if at all. The problem
is that while SQL may have a standard, how that standard is implemented
is very different. When I have worked at places which tried to be
database neutral, they inevitably give up as they find that in the end,
they needed to maintain separate SQL or have separate database
maintenance teams anyway. You will only get seamless SQL across
different databases if your SQL is very basic, in which case, you
probably don't need a full blown RDMS anyway. Most of the time, your
choice of database will be dictated by your dominate platform in the
market your application targets.  

-- 
Tim Cross

Re: Fwd: Carto ODBC_FDW issue "ERROR: Connecting to driver" from PG to DB2

[Adrian Klaver]

On 04/09/2018 02:37 PM, karthik kumar wrote:



On Mon, Apr 9, 2018 at 5:13 PM, Adrian Klaver > wrote:


On 04/09/2018 02:10 PM, karthik kumar wrote:



Assuming using this:

https://github.com/ZhengYang/odbc_fdw




I believe that should be username and password per above link:

"CREATE USER MAPPING FOR postgres
          SERVER odbc_server
          OPTIONS (username 'root', password '');
"

Yes.


Does 'yes' mean you changed this:

CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (odbc_UID
'db2inst1', odbc_PWD 'db2inst1');

to this?:

CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (username
'db2inst1', password 'db2inst1');


If i try to use username it throws error as invalid option

postgres=# CREATE SERVER driver_db5 FOREIGN DATA WRAPPER odbc_fdw 
OPTIONS (dsn 'sample');

CREATE SERVER

postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS 
(username  'db2inst1', password 'db2inst1');

ERROR:  invalid option "username"
HINT:  Valid options in this context are: 


You are using the extension from below, correct?:

https://github.com/ZhengYang/odbc_fdw

In psql what does the below show?:

\dx

What happens if you do not include the OPTIONS?



postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS 
(odbc_UID  'db2inst1', odbc_PWD 'db2inst1');

CREATE USER MAPPING



Thanks,
Karthik.



-- 
Adrian Klaver

adrian.kla...@aklaver.com 





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

Re: Fwd: Carto ODBC_FDW issue "ERROR: Connecting to driver" from PG to DB2

[Adrian Klaver]

On 04/09/2018 04:52 PM, Adrian Klaver wrote:

On 04/09/2018 02:37 PM, karthik kumar wrote:



On Mon, Apr 9, 2018 at 5:13 PM, Adrian Klaver 
> wrote:


    On 04/09/2018 02:10 PM, karthik kumar wrote:



    Assuming using this:

    https://github.com/ZhengYang/odbc_fdw




    I believe that should be username and password per above link:

    "CREATE USER MAPPING FOR postgres
          SERVER odbc_server
          OPTIONS (username 'root', password '');
    "

    Yes.


    Does 'yes' mean you changed this:

    CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (odbc_UID
    'db2inst1', odbc_PWD 'db2inst1');

    to this?:

    CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (username
    'db2inst1', password 'db2inst1');


If i try to use username it throws error as invalid option

postgres=# CREATE SERVER driver_db5 FOREIGN DATA WRAPPER odbc_fdw 
OPTIONS (dsn 'sample');

CREATE SERVER

postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS 
(username  'db2inst1', password 'db2inst1');

ERROR:  invalid option "username"
HINT:  Valid options in this context are: 


You are using the extension from below, correct?:


Answering my own question after reading the subject line I realize now 
you are using:


https://github.com/CartoDB/odbc_fdw

Am not seeing anything wrong at this time.

Did see this issue:

Support PostreSQL 10
https://github.com/CartoDB/odbc_fdw/issues/60

Are you using the latest version of the extension?



https://github.com/ZhengYang/odbc_fdw

In psql what does the below show?:

\dx

What happens if you do not include the OPTIONS?



postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS 
(odbc_UID  'db2inst1', odbc_PWD 'db2inst1');

CREATE USER MAPPING



    Thanks,
    Karthik.



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








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

Re: ERROR: found multixact from before relminmxid

[Alexandre Arruda]

2018-04-09 18:23 GMT-03:00 Peter Geoghegan :
>
> On Mon, Apr 9, 2018 at 7:01 AM, Tomas Vondra
>  wrote:
> > The bigger question is whether this can actually detect the issue. If
> > it's due to an storage issue, then perhaps yes. But if you only see
> > multixact issues consistently and nothing else, it might easily be a
> > PostgreSQL bug (in which case the checksum will be correct).
>
> You can also run amcheck. Get the version targeting earlier Postgres
> releases off Github (there are packages for most Linux systems). This
> can verify that the heap is consistent with indexes.
>
> --
> Peter Geoghegan


Hi Peter,

I ran amcheck in all index of a table and I only get empty returns.

I did some tests:

production=# reindex table fn06t;
REINDEX
production=# vacuum verbose fn06t;
INFO:  vacuuming "public.fn06t"
ERROR:  found multixact 76440919 from before relminmxid 122128619

production=# select count(*),sum(fn06vrtitu) from fn06t;
 count  |   sum
-+-
2592363 | 4833603148.1172
(1 row)

#createdb freshdb
#pg_dump -t fn06t production | psql freshdb

freshdb=# select count(*),sum(fn06vrtitu) from fn06t;
 count  |   sum
-+-
2592363 | 4833603148.1172
(1 row)

psql -c "select * from fn06t order by " production >
multixact_error_fn06t.txt
psql -c "select * from fn06t order by " freshdb > freshdb_fn06t.txt

diff points no differences in files and md5sum produces the same hash.

The question is: if a dump can read the table "correctly", why a
CLUSTER/vacuum full stops while reading them?

Best regards,

Alexandre

Re: Fwd: Carto ODBC_FDW issue "ERROR: Connecting to driver" from PG to DB2

[karthik kumar]

On Mon, Apr 9, 2018 at 7:52 PM, Adrian Klaver 
wrote:

> On 04/09/2018 02:37 PM, karthik kumar wrote:
>
>>
>>
>> On Mon, Apr 9, 2018 at 5:13 PM, Adrian Klaver > > wrote:
>>
>> On 04/09/2018 02:10 PM, karthik kumar wrote:
>>
>
> Assuming using this:
>>
>> https://github.com/ZhengYang/odbc_fdw
>>
>
>
>> I believe that should be username and password per above link:
>>
>> "CREATE USER MAPPING FOR postgres
>>   SERVER odbc_server
>>   OPTIONS (username 'root', password '');
>> "
>>
>> Yes.
>>
>>
>> Does 'yes' mean you changed this:
>>
>> CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (odbc_UID
>> 'db2inst1', odbc_PWD 'db2inst1');
>>
>> to this?:
>>
>> CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (username
>> 'db2inst1', password 'db2inst1');
>>
>>
>> If i try to use username it throws error as invalid option
>>
>> postgres=# CREATE SERVER driver_db5 FOREIGN DATA WRAPPER odbc_fdw OPTIONS
>> (dsn 'sample');
>> CREATE SERVER
>>
>> postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS
>> (username  'db2inst1', password 'db2inst1');
>> ERROR:  invalid option "username"
>> HINT:  Valid options in this context are: 
>>
>
> You are using the extension from below, correct?:
>
> https://github.com/ZhengYang/odbc_fdw
>
> In psql what does the below show?:
>
> \dx
>
>
postgres=# \dx
   List of installed extensions
   Name   | Version |   Schema   |  Description
--+-++
 odbc_fdw | 0.3.0   | public | Foreign data wrapper for accessing
remote databases using ODBC
 plpgsql  | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)


What happens if you do not include the OPTIONS?
>
>
>> postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS
>> (odbc_UID  'db2inst1', odbc_PWD 'db2inst1');
>> CREATE USER MAPPING
>>
>>
>>
postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5;
CREATE USER MAPPING

postgres=# CREATE FOREIGN TABLE
postgres-# driver_db5 (
postgres(#   id integer
postgres(# )
postgres-# SERVER driver_db5
postgres-# OPTIONS (
postgres(#   odbc_DATABASE 'sample',odbc_SCHEMA 'db2inst1'
postgres(#   );
CREATE FOREIGN TABLE

postgres=# select * from driver_db5;
ERROR:  Connecting to driver

>
>> Thanks,
>> Karthik.
>>
>>
>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: ERROR: found multixact from before relminmxid

[Peter Geoghegan]

On Mon, Apr 9, 2018 at 5:55 PM, Alexandre Arruda  wrote:
> I ran amcheck in all index of a table and I only get empty returns.

Did you try doing so with the "heapallindexed" option? That's what's
really interesting here.

-- 
Peter Geoghegan

Re: ERROR: found multixact from before relminmxid

[Peter Geoghegan]

On Mon, Apr 9, 2018 at 6:56 PM, Alexandre Arruda  wrote:
> (... and all other indexes returns null too)
>
> I tried with bt_index_check too. Same results.

That's interesting, because it tells me that you have a table that
appears to not be corrupt, despite the CLUSTER error. Also, the error
itself comes from sanity checking added to MultiXact freezing fairly
recently, in commit 699bf7d0.

You didn't say anything about regular VACUUM being broken. Do you find
that it works without any apparent issue?

I have a suspicion that this could be a subtle bug in
CLUSTER/freezing. The only heap_freeze_tuple() caller is code used by
CLUSTER, so it's not that hard to imagine a MultiXact freezing bug
that is peculiar to CLUSTER. Though I haven't thought about it in much
detail.

-- 
Peter Geoghegan

Re: ERROR: found multixact from before relminmxid

[Andres Freund]

On April 9, 2018 7:51:19 PM PDT, Peter Geoghegan  wrote:
>On Mon, Apr 9, 2018 at 6:56 PM, Alexandre Arruda 
>wrote:
>> (... and all other indexes returns null too)
>>
>> I tried with bt_index_check too. Same results.
>
>That's interesting, because it tells me that you have a table that
>appears to not be corrupt, despite the CLUSTER error. Also, the error
>itself comes from sanity checking added to MultiXact freezing fairly
>recently, in commit 699bf7d0.
>
>You didn't say anything about regular VACUUM being broken. Do you find
>that it works without any apparent issue?
>
>I have a suspicion that this could be a subtle bug in
>CLUSTER/freezing. The only heap_freeze_tuple() caller is code used by
>CLUSTER, so it's not that hard to imagine a MultiXact freezing bug
>that is peculiar to CLUSTER. Though I haven't thought about it in much
>detail.

I've not followed this thread. Possible it's the overeager check for pg 
upgraded tuples from before 9.3 that Alvaro fixed recently?

Andres

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Re: algo for canceling a deadlocked transaction

[Thomas Poty]

Hello Tom,

Thank you for the clarification!

Regards,
 Thomas

Le lun. 9 avr. 2018 à 17:04, Tom Lane  a écrit :

> Christophe Pettus  writes:
> >> On Apr 9, 2018, at 07:33, Thomas Poty  wrote:
> >> ok, and long  answer ? is it random?
>
> > It's not literally random, but from the application point of view, it's
> not predictable.  For example, it's not always the one that opened first,
> or any other consistent measure.
>
> It's whichever one runs the deadlock detector first after the circular
> wait becomes established.  For instance:
>
> * Process A takes lock L1
>
> * Process B takes lock L2
>
> * Process A tries to take lock L2, blocks
>
> * Process B tries to take lock L1, blocks (now a deadlock exists)
>
> Process A will run the deadlock detector one deadlock_timeout after
> blocking.  If that happens before B has blocked, then A will see
> no deadlock and will go back to waiting.  In that case, when B's
> own deadlock_timeout expires and it runs the deadlock detector,
> it will see the deadlock and fix it by canceling its own wait.
> On the other hand, if B started to wait less than one deadlock_timeout
> after A did, then A will be first to observe the deadlock and it will
> cancel itself, not B.
>
> So you can't predict it unless you have a lot of knowledge about
> the timing of events.  You could probably make it more predictable
> by making deadlock_timeout either very short or very long, but
> neither of those are desirable things to do.
>
> regards, tom lane
>

Re: Conflict between JSON_AGG and COPY

[David G. Johnston]

On Mon, Apr 9, 2018 at 7:14 AM, Adrian Klaver 
wrote:

> On 04/08/2018 08:44 PM, Đỗ Ngọc Trí Cường wrote:
>
> Presently, COPY TO will never emit an octal or hex-digits backslash
> sequence, but it does use the other sequences listed above for those
> control characters.
>
>
>> The second query is for the old version of PostgreSQL (9.3 and previous)
>> cause of they don't have JSON_AGG aggregate function. Since 9.4, we have
>> "JSON_AGG" already. So I want to rewrite and reduce the length of the
>> query. But it is don't work as I expected with command COPY.
>>
>
​Actually, COPY FROM will interpret the newlines just fine.  The issue here
is that most other programs that injects csv are not as intelligent and
will look at the \n as content as opposed to a control character.  Those
programs will, however, recognize an actual newline within the quoted field
as being valid data: but it doesn't appear that COPY is capable outputting
such.

David J.
​

Re: algo for canceling a deadlocked transaction

[Thomas Poty]

Hello Stephen,

> The short answer is "it's whichever one detected the deadlock."  The
> deadlock timeout fires after a lock has been held that long and if a
> deadlock is detected then the process detecting it will be canceled.

ok, and long  answer ? is it random?


> I'd strongly recommend reviewing your application and addressing
> deadlocks by changing how the application acquires locks to be
> consistent and to avoid lock escalation instead of worrying about how to
> predict a deadlock- a properly designed and written application
> shouldn't be causing deadlocks to happen in the first place.

I didn't want to predict the deadlock happening. I only want to know if it
is predictable to know which transaction will be canceled.

Thank you

2018-04-09 15:51 GMT+02:00 Stephen Frost :

> Greetings,
>
> * Thomas Poty (thomas.p...@gmail.com) wrote:
> > My question is : In case of a deadlock between 2 transaction,  how to
> know
> > which transaction will be canceled? Is it predictable?
>
> The short answer is "it's whichever one detected the deadlock."  The
> deadlock timeout fires after a lock has been held that long and if a
> deadlock is detected then the process detecting it will be canceled.
>
> I'd strongly recommend reviewing your application and addressing
> deadlocks by changing how the application acquires locks to be
> consistent and to avoid lock escalation instead of worrying about how to
> predict a deadlock- a properly designed and written application
> shouldn't be causing deadlocks to happen in the first place.
>
> Thanks!
>
> Stephen
>

Re: Conflict between JSON_AGG and COPY

[Đỗ Ngọc Trí Cường]

Dear Arian Klaver, 

I think there is a misunderstood here. 

I think that I quite understand how is the second query run. 

The question I asked here is why exporting data, which is generated by a query 
"JSON_AGG" , with command "COPY". The data contain "\n" as 2 characters instead 
of "new line" character. 

The second query is for the old version of PostgreSQL (9.3 and previous) cause 
of they don't have JSON_AGG aggregate function. Since 9.4, we have "JSON_AGG" 
already. So I want to rewrite and reduce the length of the query. But it is 
don't work as I expected with command COPY. 

Thank you and best regards, 




Đỗ Ngọc Trí Cường (Mr.) | Software Development Department | +84 28 3715 5325 


From: "Adrian Klaver"  
To: "Đỗ Ngọc Trí Cường" , "pgsql-general" 
 
Sent: Monday, April 9, 2018 12:59:44 AM 
Subject: Re: Conflict between JSON_AGG and COPY 

On 04/07/2018 03:44 AM, Đỗ Ngọc Trí Cường wrote: 


Dear all, 

I've found one case. I don't know this is a bug or I config/query some 
things wrong. 

Let I describe it. I have a table with structure and data is: 

id | username | fullname 
+-+--- 
1 | john | John 
2 | anna | Anna 
3 | sussi | Sussi 
4 | david | David Beckham 
5 | wayne | Wayne Rooney 

I want to export it to a file in JSON format so I run the query as below: 

COPY (SELECT row_to_json(t) FROM (SELECT json_agg(t1) AS "RECORDS" 
FROM test_table t1) t) TO '/home/postgres/test1.json'; 

But the result I got will include "\n" in the result: 

{"RECORDS":[{"id":1,"username":"john","fullname":"John"}, \n 
{"id":2,"username":"anna","fullname":"Anna"}, \n 
{"id":3,"username":"sussi","fullname":"Sussi"}, \n 
{"id":4,"username":"david","fullname":"David Beckham"}, \n 
{"id":5,"username":"wayne","fullname":"Wayne Rooney"}]} 

Then, I try to get the same data in the other way: 

COPY (WITH t2 AS (select row_to_json(t) as js from test_table t), 
t1 AS (SELECT ARRAY_TO_JSON(array_agg(js)) as "RECORDS" 
FROM t2) 
SELECT row_to_json(t1) FROM t1) 
TO '/home/postgres/test2.json'; 




CREATE TABLE test_table (id integer, username varchar, fullname varchar); 

INSERT INTO 
test_table 
VALUES 
(1, 'john', 'John'), 
(2, 'anna', 'Anna'), 
(3, 'sussi', 'Sussi'), 
(4, 'david', 'David Beckham'), 
(5, 'wayne', 'Wayne Rooney'); 


This can be shortened to: 

COPY 
(select array_to_json(array_agg(row_to_json(t))) FROM test_table AS t) 
TO '/home/postgres/test2.json'; 


BQ_BEGIN

And the result I got is quite match what I expect. 

{"RECORDS":[{"id":1,"username":"john","fullname":"John"},{"id":2,"username":"anna","fullname":"Anna"},{"id":3,"username":"sussi","fullname":"Sussi"},{"id":4,"username":"david","fullname":"David
 
Beckham"},{"id":5,"username":"wayne","fullname":"Wayne Rooney"}]} 


I think the COPY command does not the `\n` character for pretty in 
`json_agg` command. 

BQ_END


Well in the first case you are starting by concatenating the 5 rows in 
the table into a single row with the table rows separated by new lines: 

SELECT json_agg(t1) AS "RECORDS" FROM test_table t1; 
RECORDS 
--- 
[{"id":1,"username":"john","fullname":"John"}, + 
{"id":2,"username":"anna","fullname":"Anna"}, + 
{"id":3,"username":"sussi","fullname":"Sussi"}, + 
{"id":4,"username":"david","fullname":"David Beckham"}, + 
{"id":5,"username":"wayne","fullname":"Wayne Rooney"}] 
(1 row) 


In the second case you start by maintaining the separate table rows: 

select row_to_json(t) as js from test_table t; 
js 
 
{"id":1,"username":"john","fullname":"John"} 
{"id":2,"username":"anna","fullname":"Anna"} 
{"id":3,"username":"sussi","fullname":"Sussi"} 
{"id":4,"username":"david","fullname":"David Beckham"} 
{"id":5,"username":"wayne","fullname":"Wayne Rooney"} 
(5 rows) 

and then keeping that as an array of arrays: 

select array_agg(row_to_json(t)) from test_table t; 


{"{\"id\":1,\"username\":\"john\",\"fullname\":\"John\"}","{\"id\":2,\"username\":\"anna\",\"fullname\":\"Anna\"}","{\"id\":3,\"username\":\"sussi\",\"fullname\":\"Sussi\"}","{\"id\":4,\"username\":\"david\",\"fullname\":\"David
 
Beckham\"}","{\"id\":5,\"username\":\"wayne\",\"fullname\":\"Wayne 
Rooney\"}"} 
(1 row) 

which then gets turned back into JSON: 

select ARRAY_TO_JSON(array_agg(row_to_json(t))) from test_table t; 

[{"id":1,"username":"john","fullname":"John"},{"id":2,"username":"anna","fullname":"Anna"},{"id":3,"username":"sussi","fullname":"Sussi"},{"id":4,"username":"david","fullname":"David
 
Beckham"},{"id":5,"username":"wayne","fullname":"Wayne Rooney"}] 



BQ_BEGIN

Please help me give me your idea. Am I wrong or this is really a bug? 

Thank you and best regards, 

Đỗ Ngọc Trí*Cường*(Mr.) 

*Software Development Dept.* 



Mobile: +84 9 3800 3394  

Phone: +84 28 3715 6322  

pg_stat_all_tables not updated when VACUUM ANALYZE execution finishes

[Sebastien Arod]

Hi,

I face a surprising behaviour with VACUUM ANALYZE.

For a table with a structure like like this (and few records):
create table my_table (
my_column numeric
);

When I run the following:
VACUUM ANALYZE my_table;
SELECT relname, last_analyze, last_vacuum FROM pg_stat_all_tables where
relname='my_table';

The select returns null values for last_analyze and last_vacuum.

However if I wait a little between the end of VACUUM command and the
execution of the select the last_xxx columns have non null values.

So it looks like something is done asynchronously here but I'm not sure
what?
* Is it the vacuum itself that run asyncrhonously or the update of or the
content of the pg_stat_all_tables view?
* If I execute another query right after "VACUUM ANALYZE" is it expected
that this other query will benefit from the analyze done in the VACUUM
ANALYZE call?
* Is this a bug or a normal behaviour? (I'm using postgresql 9.6)
* Is there a way to wait for pg_stat_all_tables to be up to date? To give a
bit of context I was planning to use this information in the assertion part
of a test case I wrote to check vacuum were executed as expected but my
test is flaky because of this behaviour.


-Seb

Re: ERROR: found multixact from before relminmxid

[Alexandre Arruda]

2018-04-06 13:11 GMT-03:00 Tomas Vondra :

>
>
> On 04/06/2018 04:29 PM, Alexandre Arruda wrote:
> > 2018-04-06 9:39 GMT-03:00 Tomas Vondra  > >:
> >
> >
> >
> > On 04/06/2018 02:09 AM, Alexandre Arruda wrote:
> > > Hi,
> > >
> > > Some time ago, I had this errors frequently showed in logs after
> some
> > > autovacuum in some tables(pg 9.6). VACUUM FULL or CLUSTER in this
> tables
> > > show the same and not complete the tasks (showed by some table
> bloat
> > > select).
> > > Then, I did a full dump/restore into a new version (10.2) and
> everything
> > > is ok for a couple of months. Now, I have errors like this again:
> > >
> > > db1=# cluster pc44t;
> > >
> > > ERROR:  found multixact 134100944 from before relminmxid 192042633
> > >
> > >
> > >
> > > Like before, the only way to make the errors to desapear is by
> > > dump/reload the whole table.
> > >
> > >
> > > Thanks for any help.
> > >
> >
> > That's going to be hard, unless you still have the cluster around.
> >
> > This surely seems like some sort of data corruption issue, but
> without
> > being able to inspect the data pages it's nearly impossible to
> determine
> > what went wrong.
> >
> > We'd also need more information about what happened to the hardware
> and
> > cluster before the issues started to appear - crashes, hardware
> issues.
> > And so on.
> >
> > regards
> >
> > --
> > Tomas Vondra  http://www.2ndQuadrant.com
> > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
> >
> >
> > Hi Tomas,
> > The old cluster are gone, unfortunatly.
> >
> > This server is a 128GB ECC RAM with a dual redundant hba fiber channel
> > connect to a sotorage with Raid 6 and I don't have (apparently) any
> > errors reported.
> > Yesterday I did a test with one table:  some sum aggragates, count(*),
> > etc, then dump/reload and repeat the tests the results (of querys) are
> > the same, regarding the vacuum problem
> > thats disapeared.
> >
>
> I'm not sure I understand correctly. So you can reproduce the issue? If
> yes, how can you share the scripts/data you use (and how large is it)?
> If we could reproduce it locally, it would make the investigation much
> easier.
>
> BTW you mentioned you're using PostgreSQL 9.6 - which minor version,
> specifically?
>
>
> regards
>
>
Hi Tomas,

No, I can't reproduce. What I did is a simple way to "validate" the current
table data to see if a dump/reload
preserve them. Old postgresql was 9.6.5. The problem returns now in new
10.3 installation.

There is a way to correct this tables without a dump/reload ?

I'm thinking to reinstall cluster doing a initdb --data-checksums, but I'm
affraid about a severe performance impact.

Best regards,

Alexandre

Re: ERROR: found multixact from before relminmxid

[Tomas Vondra]

On 04/09/2018 01:49 PM, Alexandre Arruda wrote:
> 
> 
> 2018-04-06 13:11 GMT-03:00 Tomas Vondra  >:
> 
> 
> 
> On 04/06/2018 04:29 PM, Alexandre Arruda wrote:
> > 2018-04-06 9:39 GMT-03:00 Tomas Vondra  
> >  >>:
> >
> >
> >
> >     On 04/06/2018 02:09 AM, Alexandre Arruda wrote:
> >     > Hi,
> >     >
> >     > Some time ago, I had this errors frequently showed in logs
> after some
> >     > autovacuum in some tables(pg 9.6). VACUUM FULL or CLUSTER in
> this tables
> >     > show the same and not complete the tasks (showed by some
> table bloat
> >     > select).
> >     > Then, I did a full dump/restore into a new version (10.2)
> and everything
> >     > is ok for a couple of months. Now, I have errors like this
> again:
> >     >
> >     > db1=# cluster pc44t;
> >     >
> >     > ERROR:  found multixact 134100944 from before relminmxid
> 192042633
> >     >
> >     >  
> >     >
> >     > Like before, the only way to make the errors to desapear is by
> >     > dump/reload the whole table.
> >     >
> >     >
> >     > Thanks for any help.
> >     >
> >
> >     That's going to be hard, unless you still have the cluster around.
> >
> >     This surely seems like some sort of data corruption issue, but
> without
> >     being able to inspect the data pages it's nearly impossible to
> determine
> >     what went wrong.
> >
> >     We'd also need more information about what happened to the
> hardware and
> >     cluster before the issues started to appear - crashes,
> hardware issues.
> >     And so on.
> >
> >     regards
> >
> >     --
> >     Tomas Vondra                  http://www.2ndQuadrant.com
> >     PostgreSQL Development, 24x7 Support, Remote DBA, Training &
> Services
> >
> >
> > Hi Tomas,
> > The old cluster are gone, unfortunatly.
> >
> > This server is a 128GB ECC RAM with a dual redundant hba fiber channel
> > connect to a sotorage with Raid 6 and I don't have (apparently) any
> > errors reported.
> > Yesterday I did a test with one table:  some sum aggragates, count(*),
> > etc, then dump/reload and repeat the tests the results (of querys) are
> > the same, regarding the vacuum problem
> > thats disapeared.
> >
> 
> I'm not sure I understand correctly. So you can reproduce the issue? If
> yes, how can you share the scripts/data you use (and how large is it)?
> If we could reproduce it locally, it would make the investigation much
> easier.
> 
> BTW you mentioned you're using PostgreSQL 9.6 - which minor version,
> specifically?
> 
> 
> regards
> 
> 
> Hi Tomas,
> 
> No, I can't reproduce. What I did is a simple way to "validate" the
> current table data to see if a dump/reload
> preserve them. Old postgresql was 9.6.5. The problem returns now in new
> 10.3 installation.
> 

I'm confused. First you say you can't reproduce the issue, then you say
it got back on a new installation. So which is it?

> There is a way to correct this tables without a dump/reload ?
> 
> I'm thinking to reinstall cluster doing a initdb --data-checksums,
> but I'm affraid about a severe performance impact.
>  

The performance impact should be non-measurable for most workloads. It
might be a couple of percent on certain workloads/hardware, but that's
probably not your case.

The bigger question is whether this can actually detect the issue. If
it's due to an storage issue, then perhaps yes. But if you only see
multixact issues consistently and nothing else, it might easily be a
PostgreSQL bug (in which case the checksum will be correct).


regards

-- 
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Rationale for aversion to the central database?

[Peter J. Holzer]

On 2018-04-08 19:39:43 -0400, Stephen Frost wrote:
> * Alvaro Aguayo Garcia-Rada (aagu...@opensysperu.com) wrote:
> > 1. Portability. Being tied to a single database engine is not always
> > a good idea. When you write business logic in database, you have to
> > write and maintain your store procedures for every database engine
> > you want to support. That can be really complicated, and will surely
> > take pretty much time, as programming languages for different
> > databases are very different from each other. And it's permanent:
> > Every time you make a change to a store procedure, you must make
> > that change for every supported database.
> 
> The portability claim tends to be both a false one and often, when
> realized, results in a solution where you aren't using the database for
> anything complicated and you'd be better off with a much simpler data
> store.  You also don't actually offer any justification for the claim
> that being tied to a single database engine is not always a good idea-
> why is that?  With commercial databases it tends to be because you are
> at the behest of some very large commercial company- but that isn't an
> issue with PostgreSQL.

Many companies don't like to support multiple database engines. There is
the license issue, true, but having DBAs is even more important. If you
are an Oracle shop you have your Oracle DBAs, and they know how to make
backups (and how to restore them), how to monitor the database, how to
track down performance issues, etc. If some application needs MS-SQL or
PostgreSQL or MySQL, they won't have that expertise, so they will have
to be trained, or you need additional DBAs (maybe on a part-time or
support contract basis). So all other things being equal, companies will
prefer applications which work with databases they already use.

So if you are an application developer, it makes sense to develop your
application to work with several databases. It vastly expands your
potential clientele.

At least that was the situation 10 years ago. These days much software
is offered as a service. If the customer sees only a REST API and
doesn't have to host the database on their own servers, they won't care
about the RDBMS underneath.

hp


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


signature.asc
Description: PGP signature

Re: Conflict between JSON_AGG and COPY

[Adrian Klaver]

On 04/08/2018 08:44 PM, Đỗ Ngọc Trí Cường wrote:

Dear Arian Klaver,

I think there is a misunderstood here.

I think that I quite understand how is the second query run.

The question I asked here is why exporting data, which is generated by a 
query "JSON_AGG" , with command "COPY". The data contain "\n" as 2 
characters instead of "new line" character.


https://www.postgresql.org/docs/10/static/sql-copy.html

File Formats
Text Format

"
\n  Newline (ASCII 10)

Presently, COPY TO will never emit an octal or hex-digits backslash 
sequence, but it does use the other sequences listed above for those 
control characters.


...

COPY TO will terminate each row with a Unix-style newline (“\n”).
"



The second query is for the old version of PostgreSQL (9.3 and previous) 
cause of they don't have JSON_AGG aggregate function. Since 9.4, we have 
"JSON_AGG" already. So I want to rewrite and reduce the length of the 
query. But it is don't work as I expected with command COPY.


Thank you and best regards,


Đỗ Ngọc Trí*Cường*(Mr.) | *Software Development Department*| +84 28 3715 
5325


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

Re: pg_stat_all_tables not updated when VACUUM ANALYZE execution finishes

[Adrian Klaver]

On 04/09/2018 03:37 AM, Sebastien Arod wrote:

Hi,

I face a surprising behaviour with VACUUM ANALYZE.

For a table with a structure like like this (and few records):
create table my_table (
     my_column numeric
);

When I run the following:
VACUUM ANALYZE my_table;
SELECT relname, last_analyze, last_vacuum FROM pg_stat_all_tables where 
relname='my_table';


The select returns null values for last_analyze and last_vacuum.

However if I wait a little between the end of VACUUM command and the 
execution of the select the last_xxx columns have non null values.


So it looks like something is done asynchronously here but I'm not sure 
what?


https://www.postgresql.org/docs/10/static/monitoring-stats.html

28.2.2. Viewing Statistics

"When using the statistics to monitor collected data, it is important to 
realize that the information does not update instantaneously. Each 
individual server process transmits new statistical counts to the 
collector just before going idle; so a query or transaction still in 
progress does not affect the displayed totals. Also, the collector 
itself emits a new report at most once per PGSTAT_STAT_INTERVAL 
milliseconds (500 ms unless altered while building the server). So the 
displayed information lags behind actual activity. However, 
current-query information collected by track_activities is always 
up-to-date.

"

* Is it the vacuum itself that run asyncrhonously or the update of or 
the content of the pg_stat_all_tables view?
* If I execute another query right after "VACUUM ANALYZE" is it expected 
that this other query will benefit from the analyze done in the VACUUM 
ANALYZE call?

* Is this a bug or a normal behaviour? (I'm using postgresql 9.6)
* Is there a way to wait for pg_stat_all_tables to be up to date? To 
give a bit of context I was planning to use this information in the 
assertion part of a test case I wrote to check vacuum were executed as 
expected but my test is flaky because of this behaviour.



-Seb





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

Re: Rationale for aversion to the central database?

[Peter J. Holzer]

On 2018-04-08 14:39:49 -0700, Guyren Howe wrote:
> I am a Rails developer at a medium-large size company. I’ve mostly worked at
> smaller companies. I’ve some exposure to other web development communities.
> 
> When it comes to databases, I have universally encountered the attitude that
> one should treat the database as a dumb data bucket. There is a *very* strong
> aversion to putting much of any business logic in the database. I encounter
> substantial aversion to have multiple applications access one database, or 
> even
> the reverse: all abstraction should be at the application layer.

Like Tim I would like to make a case for finding a middle ground. The
sweet spot may be different from project to project and it is certainly
different for different teams or companies.

I'll try to outline what I like to solve within the database and what I
like to put into the application, and why:

Everything which is easy to specify *declaratively* goes into the
database: Tables (of course), views, constraints, etc. These are
basically type declarations for the data which is stored in the
database, so it belongs there.

Simple stored procedures and triggers go into the database, too. They
should make using the database easier and should not surprise the user.
This is mostly optional.

Anything I would consider "application logic" (when the user does X,
then the system should do Y) goes into the application. There is often a
distinction between a backend (or data access layer) and a frontend. The
backend could theoretically be within the database and be called via
stored procedures, but I find it much easier to test and debug code
outside of the database (this may be just my lack of knowledge about
available tools).

In my applications I use SQL heavily. RDBMs are good at processing
queries, so use them for that. If all you want is a key-value store,
don't use PostgreSQL. I'm not very fond of ORMs. I know what I want to
do and can express it in SQL. An ORM makes me translate that into a
different (and usually inferior) query language, which is then
translated back into SQL. That doesn't make things easier for me.


> My best theory is that these communities developed at a time when Windows was
> more dominant, and just generally it was *significantly* easier to use MySQL
> than Postgres for many, particularly new, developers.

I come from Oracle, not MySQL, But I have also used MySQL, and I guess
the very wide gap in capabilities between Oracle and MySQL made me
cautious about putting too much into the database. There is also the
expectation that you should be able to use a different database engine
(SQL is a standard, right?) just like you should be able to use a
different C compiler, but in practice that never works. And of course I
wasn't very impressed with PL/SQL. (PostgreSQL gives you a much wider
range of languages for stored procedures than Oracle, but PL/PerlU still
isn't quite the same as Perl (And I suspect it's the same for Python).

hp

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


signature.asc
Description: PGP signature

Re: Rationale for aversion to the central database?

[Ray Cote]

Maintaining your database logic in version control and versioning the
deployed code can be a bit problematic.

Conversely, providing a standardized pgsql module through which data is
updated and retrieved can help standardize access logic across multiple
languages and libraries.

And I concur that database portability is a thing people like to discuss,
but rarely occurs.
Portability is important for general ORM tools, less so for corporate
projects (there are always exceptions).

Like any tool, needs to be used wisely.
I've worked on a project that has 10s of thousands of lines of business
logic in the database and it makes perfect sense for that environment.
--Ray

algo for canceling a deadlocked transaction

[Thomas Poty]

Good afternoon,

My question is : In case of a deadlock between 2 transaction,  how to know
which transaction will be canceled? Is it predictable?

I have tried to look into sources but i have found nothing. ( probably, i
am the problem)

Regards,


Thomas

Re: algo for canceling a deadlocked transaction

[Stephen Frost]

Greetings,

* Thomas Poty (thomas.p...@gmail.com) wrote:
> My question is : In case of a deadlock between 2 transaction,  how to know
> which transaction will be canceled? Is it predictable?

The short answer is "it's whichever one detected the deadlock."  The
deadlock timeout fires after a lock has been held that long and if a
deadlock is detected then the process detecting it will be canceled.

I'd strongly recommend reviewing your application and addressing
deadlocks by changing how the application acquires locks to be
consistent and to avoid lock escalation instead of worrying about how to
predict a deadlock- a properly designed and written application
shouldn't be causing deadlocks to happen in the first place.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Rationale for aversion to the central database?

[Melvin Davidson]

On Mon, Apr 9, 2018 at 9:45 AM, Ray Cote 
wrote:

> Maintaining your database logic in version control and versioning the
> deployed code can be a bit problematic.
>
> Conversely, providing a standardized pgsql module through which data is
> updated and retrieved can help standardize access logic across multiple
> languages and libraries.
>
> And I concur that database portability is a thing people like to discuss,
> but rarely occurs.
> Portability is important for general ORM tools, less so for corporate
> projects (there are always exceptions).
>
> Like any tool, needs to be used wisely.
> I've worked on a project that has 10s of thousands of lines of business
> logic in the database and it makes perfect sense for that environment.
> --Ray
>
>
>
>



*While this discussion is very interesting, it is important to realize that
ultimately, it is the needs and policy of the company that decides how that
database is used. The primary purpose of a DBA is to install the proper
security, protect the integrity of the data and maintain
performance.Determining whether it is better to place business logic in the
database or the application is strictly on a case by case basis. *
-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!