Re: [GENERAL] a JOIN to a VIEW seems slow

[Frank Millman]

Merlin Moncure wrote:
On Thu, Sep 14, 2017 at 8:17 AM, Pavel Stehule  wrote:
>
> >
> > The PostgreSQL cannot to push join - in slow case, the UNIONS should be done
> > first - and it requires full scan ar_tran_inv - used filter (posted AND
> > (deleted_id = 0) is not too effective - maybe some composite or partial
> > index helps.
> 
> In my testing JOINS can push through UNION ALL.   Why do we need to
> materialize  union first?  What version is this?
> 

I am using version 9.4.4 on Fedora 22.
Frank Millman

Re: [GENERAL] a JOIN to a VIEW seems slow

[Merlin Moncure]

On Thu, Sep 14, 2017 at 8:17 AM, Pavel Stehule  wrote:
> 2017-09-14 15:09 GMT+02:00 Pavel Stehule :
>>
>>
>>
>> 2017-09-14 14:59 GMT+02:00 Frank Millman :
>>>
>>> Pavel Stehule wrote:
>>>
>>> 2017-09-14 10:14 GMT+02:00 Frank Millman :

 Hi all

 This is a follow-up to a recent question I posted regarding a slow
 query. I thought that the slowness was caused by the number of JOINs in the
 query, but with your assistance I have found the true reason. I said in the
 previous thread that the question had become academic, but now that I
 understand things better, it is no longer academic as it casts doubt on my
 whole approach.

 I have split my AR transaction table into three physical tables –
 ar_tran_inv, ar_tran_crn, ar_tran_rec. I will probably add others at some
 point, such as ar_tran_jnl.

 I then create a VIEW to view all transactions combined. The view is
 created like this -

 CREATE VIEW ar_trans AS
   SELECT ‘ar_inv’ AS tran_type, row_id AS tran_row_id, tran_number ...
 FROM ar_tran_inv WHERE posted = ‘1’
   UNION ALL
   SELECT ‘ar_crn’ AS tran_type, row_id AS tran_row_id, tran_number ...
 FROM ar_tran_crn WHERE posted = ‘1’
   UNION ALL
   SELECT ‘ar_rec’ AS tran_type, row_id AS tran_row_id, tran_number ...
 FROM ar_tran_rec WHERE posted = ‘1’

 I have another table called ‘ar_trans_due’, to keep track of outstanding
 transactions. All of the three transaction types generate entries into this
 table. To identify the source of the transaction, I have created columns in
 ar_trans_due called ‘tran_type’ and ‘tran_row_id’. After inserting a row
 into ‘ar_tran_inv’, I invoke this -

   INSERT INTO ar_trans_due (tran_type, tran_row_id, ...) VALUES
 (‘ar_inv’, ar_tran_inv.row_id, ...), and similar for the other transaction
 types. It is handled by a Python program, and it all happens within a
 transaction.

 When I view a row in ar_trans_due, I want to retrieve data from the
 source transaction, so I have this -

   SELECT * FROM ar_trans_due a
   LEFT JOIN ar_trans b ON b.tran_type = a.tran_type AND b.tran_row_id =
 a.tran_row_id

 I understand that PostgreSQL must somehow follow a path from the view
 ‘ar_trans’ to the physical table ‘ar_tran_inv’, but I assumed it would
 execute the equivalent of SELECT * FROM ar_tran_inv WHERE row_id =
 a.tran_row_id AND posted = ‘1’.

 If this was the case, it would be an indexed read, and very fast.
 Instead, according to EXPLAIN, it performs a sequential scan of the
 ‘ar_tran_inv’ table.

 It also scans ‘ar_tran_crn’ and ‘ar_tran_rec’, but EXPLAIN shows that it
 uses a Bitmap Heap Scan on those. I assume that is because the tables are
 currently empty.

 Is this analysis correct?
>>>
>>>
>>> please, send EXPLAIN ANALYZE result :)


>>>
>>>
>>> I tried to reduce this to its simplest form.
>>>
>>> Here is a SQL statement -
>>>
>>> SELECT *
>>> FROM ccc.ar_trans_due a
>>> LEFT JOIN ccc.ar_trans b ON
>>> b.tran_type = a.tran_type AND b.tran_row_id = a.tran_row_id
>>> WHERE a.row_id = 1
>>>
>>> ar_trans_due is a physical table, ar_trans is a view.
>>>
>>> It takes about 28ms. Here is the explain -
>>> https://explain.depesz.com/s/8YY
>>>
>>>
>
>
> The PostgreSQL cannot to push join - in slow case, the UNIONS should be done
> first - and it requires full scan ar_tran_inv - used filter (posted AND
> (deleted_id = 0) is not too effective - maybe some composite or partial
> index helps.


In my testing JOINS can push through UNION ALL.   Why do we need to
materialize  union first?  What version is this?

merlin


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

Re: [GENERAL] reload postgresql with invalid pg_hba.conf

[Tom Lane]

"David G. Johnston"  writes:
> On Mon, Sep 18, 2017 at 12:36 PM, Tom Lane  wrote:
>> I wouldn't say it's desired behavior, exactly, but there's no very
>> good way to improve it.  pg_ctl has no visibility into what the postmaster
>> is thinking.

> The function signature (and docs) for pg_reload_conf makes the OPs
> interpretation understandable.

Yeah.  After more thought, it occurs to me that now that we did f13ea95f9,
we could improve matters by extending that concept: the postmaster could
record its last reload time in postmaster.pid along with a success/failure
flag, and pg_ctl could watch that file to detect what happened.

(No, I'm not volunteering to write the patch.)

regards, tom lane


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

Re: [GENERAL] reload postgresql with invalid pg_hba.conf

[David G. Johnston]

On Mon, Sep 18, 2017 at 12:36 PM, Tom Lane  wrote:

> jotpe  writes:
> > A system administration applied an invalid line into the pg_hba.conf
> > file and called "service postgresql reload". Since that command doesn't
> > return any error and leaves with exit code 0 it seams that new
> > configuration was applied.
>
> > Of course postgresql added a warning to the log file: "pg_hba.conf not
> > reloaded".
> > But even when I execute SELECT pg_reload_conf(), true is returned.
>
> > Is this the desired behavior?
>
> I wouldn't say it's desired behavior, exactly, but there's no very
> good way to improve it.  pg_ctl has no visibility into what the postmaster
> is thinking.
>

The function signature (and docs) for pg_reload_conf makes the OPs
interpretation understandable.  The docs and intuition would lead one to
assume that "true" means the reload was successful and "false" means it was
not (possibly with reasons emitted as notices/warnings/errors).​  But all
it tells us is whether a signal "was sent" and not whether it was
successfully acted upon by the other party.

Something like the attached makes this dynamic stand out.

https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL

Cancel and terminate backends do have conditional return values so those
two seem OK.

pg_rotate_logfile() I suppose might want to be clarified here as well
though I suspect it is less prone to complications that pg_reload_conf is.

The description of pg_ctl doesn't lead to the same kind of assumptions
being made (i.e., it only speaks of sending a signal and has no declared
return value) though maybe an additional sentence wouldn't hurt...?

The docs on configuring the server to speak to this overall flow and what
happens when invalid values are encountered (i.e., they are ignored)

https://www.postgresql.org/docs/current/static/config-setting.html#CONFIG-INCLUDES

David J.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 641b3b8f4e..9ac7bf1e87 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18373,7 +18373,7 @@ SELECT set_config('log_statement_stats', 'off', false);
 pg_reload_conf()
 
boolean
-   Cause server processes to reload their configuration 
files
+   Cause server processes to attempt reloading their configuration 
files
   
   

@@ -18420,7 +18420,10 @@ SELECT set_config('log_statement_stats', 'off', false);

 pg_reload_conf sends a SIGHUP signal
 to the server, causing configuration files
-to be reloaded by all server processes.
+to be reloaded by all server processes.  The return value only indicates
+whether the signal was sent successfully to the postmaster.  It does not
+say whether the reload was performed and propogated successfully by
+the postmaster.

 


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

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

[Rafal Pietrak]

W dniu 18.09.2017 o 17:26, Jehan-Guillaume (ioguix) de Rorthais pisze:
> On Sat, 16 Sep 2017 12:15:46 +0200
> Rafal Pietrak  wrote:
> 
>> W dniu 16.09.2017 o 09:33, Jehan-Guillaume de Rorthais pisze:

[-]
> 
> About FK, just look at pgsql trigger code around real FK and adjust to your
> need by creating custom FK trigger either in plpgsql or C. IIRC, there's a
> contrib module doing FK as well, look at "contrib/spi/refint.c".
> 
> 

OK. I'll look into it, too. But the "discovery" of
pg_advisory_xact_lock() itself is a revelation for me :)

Thenx,

-R


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

Re: [GENERAL] reload postgresql with invalid pg_hba.conf

[Tom Lane]

jotpe  writes:
> A system administration applied an invalid line into the pg_hba.conf 
> file and called "service postgresql reload". Since that command doesn't 
> return any error and leaves with exit code 0 it seams that new 
> configuration was applied.

> Of course postgresql added a warning to the log file: "pg_hba.conf not 
> reloaded".
> But even when I execute SELECT pg_reload_conf(), true is returned.

> Is this the desired behavior?

I wouldn't say it's desired behavior, exactly, but there's no very
good way to improve it.  pg_ctl has no visibility into what the postmaster
is thinking.

regards, tom lane


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

[GENERAL] reload postgresql with invalid pg_hba.conf

[jotpe]

A system administration applied an invalid line into the pg_hba.conf 
file and called "service postgresql reload". Since that command doesn't 
return any error and leaves with exit code 0 it seams that new 
configuration was applied.


Of course postgresql added a warning to the log file: "pg_hba.conf not 
reloaded".

But even when I execute SELECT pg_reload_conf(), true is returned.

Is this the desired behavior?

Best regards.


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

[GENERAL] Call a builtin function from Devart's linqConnect

[marcelo]

Sorry I'm asking this question to the list, but the Devart's 
documentation is very sparse.


I would like to call pg_try_advisory_lock( bigint ) and corresponding 
pg_advisory_unlock( bigint ) from my DAL library.


Does someone use this ORM, and call server functions using it? How is it 
done, and how to get the result?


TIA

Marcelo


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

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

[Jehan-Guillaume (ioguix) de Rorthais]

On Sat, 16 Sep 2017 12:15:46 +0200
Rafal Pietrak  wrote:

> W dniu 16.09.2017 o 09:33, Jehan-Guillaume de Rorthais pisze:
> >   
> [-]
> > 
> > I wrote something about this some years ago, this might do the trick for
> > you, maybe with some adjustments depending on your schema. The main idea
> > should help anyway. See:
> > 
> > http://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html
> >   
> 
> For the document management schema I need a way to avoid partitioning of
> "process-tables", and still be able to FK from it into the document
> class forest of inherited tables. Just uniqueness of IDs across that
> forest is not enough ... although nice to have, I admit. Good & handy
> article. I'll keep a reference to it.

Well, as I wrote in my email, it requiers some adjustments IRW your schema,
meaning you'll have to hack around to make it fit your need.

About FK, just look at pgsql trigger code around real FK and adjust to your
need by creating custom FK trigger either in plpgsql or C. IIRC, there's a
contrib module doing FK as well, look at "contrib/spi/refint.c".


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

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

[Melvin Davidson]

On Mon, Sep 18, 2017 at 9:30 AM, Ron Johnson  wrote:

> On 09/18/2017 08:17 AM, Melvin Davidson wrote:
> [snip]
>
> I don't have any specific suggestion for an additional column, other than
> Berend's idea. However, I strongly advise against the use
> of ENUM's. They can create a major problem in the event one needs to be
> removed.
>
>
> Because it will internally renumber them?
>
> --
> World Peace Through Nuclear Pacification
>
>
>Because it will internally renumber them?
No, because while there is a facility to ADD a value to an enum, there is
none to delete/drop a value.


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

[Ron Johnson]

On 09/18/2017 08:17 AM, Melvin Davidson wrote:
[snip]
I don't have any specific suggestion for an additional column, other than 
Berend's idea. However, I strongly advise against the use
of ENUM's. They can create a major problem in the event one needs to be 
removed.


Because it will internally renumber them?

--
World Peace Through Nuclear Pacification

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

[Melvin Davidson]

On Mon, Sep 18, 2017 at 7:59 AM, Berend Tober  wrote:

> Alexander Farber wrote:
>
>> Good evening,
>>
>> In a word game I store all player moves in the table:
>>
>> CREATE TYPE words_action AS ENUM (
>> 'play',
>> 'skip',
>> 'swap',
>> 'resign',
>> 'ban',
>> 'expire'
>> );
>>
>> CREATE TABLE words_moves (
>> mid BIGSERIAL PRIMARY KEY,
>> action  words_action NOT NULL,
>> gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
>> uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
>> played  timestamptz NOT NULL,
>> tiles   jsonb,
>> score   integer CHECK(score >= 0)
>> );
>>
>> I could run a cron job on all moves played each day and select the
>> "spectacular" ones by it,
>> i.e. when a very big score has been achieved in the move or all 7 tiles
>> have been played...
>>
>> Then I (as admin of the game) would manually review the daily mails sent
>> by that cronjob and
>> select the few I have found interesting - for later publishing them as
>> "daily puzzle" in my day.
>>
>> However I don't want to do the reviewing every day as that would be
>> tedious, but more like once
>> per week and then select several such moves at once (for the future
>> dates).
>>
>> My question (and thanks for reading my mail sofar) is: which column would
>> you add to the
>> words_moves table for such a purpose?
>>
>> If I add a boolean (is a daily puzzle move: true or false) - then it is
>> difficult to keep the
>> order of the daily puzzles, I think.
>>
>> If I add a timestamptz, then to which date to set it, when I do my manual
>> review once a week?
>>
>> I need to add a useful column, so that it would be easy to me to create a
>> web script which would
>> display today's and all past "daily puzzle" records - and wouldn't change
>> the already published
>> puzzles...
>>
>> If you have a good idea here, please share with me. If not, sorry for the
>> maybe offtopic
>> question.
>>
>
> I like the idea of a new column in words_games that allows nulls and to be
> filled in subsequently with the review date, but here's another idea to
> consider:
>
> If you have another place to store control information, you could store
> the mid value of the last-reviewed  words_moves table row. That serial
> column also keeps track of the order, btw.
>
> Or maybe you define another table capturing more detail, if you need it,
> such as
>
> CREATE TABLE spectacular_moves (
> mid BIGINTEGER REFERENCES words_games,
> review_date  timestamptz NOT NULL,
> publication_date timestamptz /*NULL allowed ... date to be filled
> in subsequently */,
> );
>
> Or those last two columns could both be appended to the word_games table,
> again, allowing NULL, but then filled in as the events occur.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

I don't have any specific suggestion for an additional column, other than
Berend's idea. However, I strongly advise against the use
of ENUM's. They can create a major problem in the event one needs to be
removed. It is a lot easier and simpler to use a
Foreign Key table instead.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

[Berend Tober]

Alexander Farber wrote:

Good evening,

In a word game I store all player moves in the table:

CREATE TYPE words_action AS ENUM (
'play',
'skip',
'swap',
'resign',
'ban',
'expire'
);

CREATE TABLE words_moves (
mid BIGSERIAL PRIMARY KEY,
action  words_action NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
played  timestamptz NOT NULL,
tiles   jsonb,
score   integer CHECK(score >= 0)
);

I could run a cron job on all moves played each day and select the 
"spectacular" ones by it,
i.e. when a very big score has been achieved in the move or all 7 tiles have 
been played...

Then I (as admin of the game) would manually review the daily mails sent by 
that cronjob and
select the few I have found interesting - for later publishing them as "daily 
puzzle" in my day.

However I don't want to do the reviewing every day as that would be tedious, 
but more like once
per week and then select several such moves at once (for the future dates).

My question (and thanks for reading my mail sofar) is: which column would you 
add to the
words_moves table for such a purpose?

If I add a boolean (is a daily puzzle move: true or false) - then it is 
difficult to keep the
order of the daily puzzles, I think.

If I add a timestamptz, then to which date to set it, when I do my manual 
review once a week?

I need to add a useful column, so that it would be easy to me to create a web 
script which would
display today's and all past "daily puzzle" records - and wouldn't change the 
already published
puzzles...

If you have a good idea here, please share with me. If not, sorry for the maybe 
offtopic
question.


I like the idea of a new column in words_games that allows nulls and to be filled in subsequently 
with the review date, but here's another idea to consider:


If you have another place to store control information, you could store the mid value of the 
last-reviewed  words_moves table row. That serial column also keeps track of the order, btw.


Or maybe you define another table capturing more detail, if you need it, such as

CREATE TABLE spectacular_moves (
mid BIGINTEGER REFERENCES words_games,
review_date  timestamptz NOT NULL,
publication_date timestamptz /*NULL allowed ... date to be filled in 
subsequently */,
);

Or those last two columns could both be appended to the word_games table, again, allowing NULL, but 
then filled in as the events occur.




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

Re: [GENERAL] pg_rewind issue

[Michael Paquier]

On Mon, Sep 18, 2017 at 1:36 PM, James Sewell  wrote:
> When Iook at timeline 15 I see this:
>
> 20  C74/4500no recovery target specified+

This refers to timeline 20. Are there other entries in this history
file? Isn't timeline 15 a direct parent of timeline 20?

> As 00150C7300A9 > 00150C740044  I'm confused why
> this WAL would be required? It's never been created on either server
> (although the same suffix does exist in timeline 14).

Your math is incorrect here. For me 00150C7300A9 <
00150C740044, C73 being generated *before* C74.

> The contents of this email are confidential and may be subject to legal or
> professional privilege and copyright. No representation is made that this
> email is free of viruses or other defects. If you have received this
> communication in error, you may not copy or distribute any part of it or
> otherwise disclose its contents to anyone. Please advise the sender of your
> incorrect receipt of this correspondence.

The contents of this mailing list are public.
-- 
Michael


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

Re: [GENERAL] BDR, near xid wraparound, a lot of files in pg_subtrans directory

[milist ujang]

Hi Craig,

So, is it safe to drop those list from this query output?

select riname from pg_replication_identifier where riname not in
(select external_id from pg_replication_identifier_progress);

I cannot read pg_get_replication_identifier_progress function, is it likely
c function?




On Fri, Sep 15, 2017 at 11:14 AM, Craig Ringer 
wrote:

> On 15 September 2017 at 11:46, milist ujang 
> wrote:
>
>> Hi Craig,
>>
>> Thanks again for pointing to inactive replication slot.
>> After inactive replication slot been dropped, the relfrozenxid now moving.
>>
>> I wonder if  replication identifier will have some issue if left
>> un-chained? since at other side there are inactive replication identifier.
>>
>
> No, that's harmless.
>
> However, if there's still an "other side" at all, you presumably have
> broken replication.
>
>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>



-- 
regards

ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab