[GENERAL] Sequences, txids, and serial order of transactions

[Christian Ohler]

Hi,

we have a use case similar to auditing packages like pgMemento or Audit
Trigger 91plus – we are looking to keep an ordered history of certain write
transactions.  I'm trying to understand the trade-offs between different
ways of getting that order, i.e., assigning numbers to transactions
(ideally strictly monotonic, modulo concurrency).  All of our transactions
are serializable (for now).

The two main candidates I'm aware of are txid_current() or nextval() of a
sequence; but perhaps there are other mechanisms we should be considering.

Some observations and questions from my investigations so far (and please
correct me if any of this is wrong):

(1) managing a counter in a table would essentially eliminate concurrency,
so we're not too interested in that

(2) the orders produced by txid_current and a sequence can be different
(unsurprisingly).  (If it was desirable to make them match, we could
probably do so by briefly holding a lock while we call both txid_current
and nextval – seems like this shouldn't limit concurrency too much.  Or
would it?  Is one of them potentially slow?)

(3) logical replication has mechanisms to keeps sequences in sync, but not
txid_current (unsurprisingly)

(4) behaviors like
http://permalink.gmane.org/gmane.comp.db.postgresql.bugs/35636 make me
think that monotonicity of txid_current is not something we should bet on

(5) Postgres can give us a "high watermark" ("no transactions with IDs
below this number are still in-flight") for txid_current (using
txid_snapshot_xmin(txid_current_snapshot())), but has no equivalent feature
for sequences

(6) neither txid_current nor a sequence give us a valid serial order of the
transactions

(7) given that we can't get a valid serial order, what guarantees can we
get from the ordering?  I'm not entirely sure what to look for, but at a
minimum, it seems like we want writes that clobber each other to be
correctly ordered.  Are they, for both txid_current and for sequences?  My
guess was "yes" for txids (seems intuitive but just a guess) and "no" for
sequences (because
https://www.postgresql.org/docs/current/static/functions-sequence.html
mentions that sequences are non-transactional); but for sequences, I
couldn't immediately construct a counterexample and am wondering whether
that's by design.  Specifically, it seems that Postgres acquires the
snapshot for the transaction (if it hasn't already) when I call nextval(),
and as long as the snapshot is acquired before the sequence is incremented,
I suspect that this guarantees ordering writes.  Does it?

(8) ...and is the snapshot acquired before or after the increment?  (Is it
acquired as soon as Postgres sees SELECT, before even evaluating
nextval()?  I think that's what I'm seeing.  Is that something we can rely
on, or should we SELECT txid_current_snapshot() before SELECT nextval() to
be on the safe side?)

(9) are there other important properties that one order satisfies but the
other doesn't, or that neither satisfies but that we should be aware of?


(3) and (4) seem like strong reasons to go with a sequence, as long as we
can live without (5) and figure out (7) and (8).

Any help appreciated,
Christian.

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

[Artur Zakirov]

2016-06-11 13:47 GMT+03:00 Greg Navis :

> I made some progress but I'm stuck. I'm focused on GiST for now. Please
> ignore sloppy naming for now.
>
> I made the following changes to pg_trgm--1.2.sql:
>
> CREATE TYPE pg_trgm_match AS (match TEXT, threshold REAL);
>
> CREATE OR REPLACE FUNCTION trgm_check_match(string TEXT, match
> pg_trgm_match) RETURNS bool AS $$
> BEGIN
> RETURN match.match <-> string <= 1 - match.threshold;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE OPERATOR %%(leftarg = text, rightarg = pg_trgm_match,
> procedure=trgm_check_match);
>
> ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD
>OPERATOR9   %% (text,
> pg_trgm_match);
>

You can overload existing % operator:

ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD
OPERATOR9   % (text, pg_trgm_match);


>
> It does indeed make PostgreSQL complain about undefined strategy 9. I
> added the following define to trgm.h:
>
> #define ThresholdStrategyNumber 9
>
> It seems StrategyNumber is used in gtrgm_consistent and gtrgm_distance.
>
> In gtrgm_consistent, I need change the way `nlimit` is obtained:
>
> nlimit = (strategy == SimilarityStrategyNumber) ?
> similarity_threshold : word_similarity_threshold;
>
> I need to add a case for ThresholdStrategyNumber and extract `nlimit` from
> the argument of `pg_trgm_match`. I'm not sure what to do in
> `gtrgm_distance`.
>
> My questions:
>
> 1a. Is it possible to make `gtrgm_consistent` accept `text` or
> `pg_trgm_match` as the second argument?
>

I think you can change definition of the gtrgm_consistent() in .sql file in
CREATE FUNCTION and CREATE OPERATOR CLASS commands to:

gtrgm_consistent(internal,anynonarray,smallint,oid,internal)

But I do not sure that anynonarray is good here.


> 1b. What's the equivalent of `match.match` and `match.threshold` (where
> `match` is a `pg_trgm_match`) in C?
>

After changing the definition you can extract values from composite type in
the gtrgm_consistent(). I think the code in the beginning of function may
looks like this:

if (strategy == SimilarityStrategyNumber ||
strategy == WordSimilarityStrategyNumber)
{
query = PG_GETARG_TEXT_P(1);
nlimit = (strategy == SimilarityStrategyNumber) ?
similarity_threshold : word_similarity_threshold;
}
else if (strategy == ThresholdStrategyNumber)
{
HeapTupleHeader query_match = PG_GETARG_HEAPTUPLEHEADER(1);
Oid tupType = HeapTupleHeaderGetTypeId(query_match);
int32 tupTypmod = HeapTupleHeaderGetTypMod(query_match);
TupleDesc tupdesc = lookup_rowtype_tupdesc(tupType, tupTypmod);
HeapTupleData tuple;
bool isnull;

tuple.t_len = HeapTupleHeaderGetDatumLength(query_match);
ItemPointerSetInvalid(&(tuple.t_self));
tuple.t_tableOid = InvalidOid;
tuple.t_data = query_match;

query = DatumGetTextP(fastgetattr(, 1, tupdesc, ));
nlimit = DatumGetFloat4(fastgetattr(, 2, tupdesc, ));

ReleaseTupleDesc(tupdesc);
}
else
query = PG_GETARG_TEXT_P(1);

After this code you should execute the query using index:

select t,similarity(t,'qwertyu0988') as sml from test_trgm where t %
row('qwertyu0988', 0.6)::pg_trgm_match;

I got the query from the regression test. And of course the code need to be
checked for bugs.


> 2. What to do with `gtrgm_distance`?
>

You do not need to change gtrgm_distance(). It is used only in ORDER BY
clause to calculate distances. To calculate distance you do not need
threshold.


>
> Thanks for help.
> --
> Greg Navis
> I help tech companies to scale Heroku-hosted Rails apps.
> Free, biweekly scalability newsletter for SaaS CEOs
> 
>
>


-- 
Artur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

Re: [GENERAL] pgAdmin 4 beta not working on Windows 10

[Adrian Klaver]

On 06/11/2016 01:39 AM, Thomas Kellerer wrote:

Dave Page schrieb am 10.06.2016 um 16:48:

I'm pleased to announce that the release of pgAdmin 4 v1.0 Beta 1 for
testing. You can find more details on the website:

Announcement: https://www.pgadmin.org/

Documentation: https://www.pgadmin.org/docs4/dev/index.html

Downloads: https://www.pgadmin.org/download/

Bug tracker: https://redmine.postgresql.org/projects/pgadmin4/issues
(requires a PostgreSQL community login)



I don't have a community login, so I'm posting that here:


FYI, if you want one:

https://www.postgresql.org/account/signup/



On Windows 10 64bit it doesn't start.

When running pgadmin4.exe I get a dialog

   "Fatal Error"
   An Error occurred initialising the application server:
Failed to launche the application server, server thread exiting.

Then it prompts me for a "Python Path" and an "Application Path".

I pointed that to

  c:\Program Files (x86)\pgAdmin 4\v1\runtime\python27.dll
  and
  c:\Program Files (x86)\pgAdmin 4\v1\runtime\pgAdmin4.exe

but after that, nothing happens.









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


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

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

[Greg Navis]

I made some progress but I'm stuck. I'm focused on GiST for now. Please
ignore sloppy naming for now.

I made the following changes to pg_trgm--1.2.sql:

CREATE TYPE pg_trgm_match AS (match TEXT, threshold REAL);

CREATE OR REPLACE FUNCTION trgm_check_match(string TEXT, match
pg_trgm_match) RETURNS bool AS $$
BEGIN
RETURN match.match <-> string <= 1 - match.threshold;
END;
$$ LANGUAGE plpgsql;

CREATE OPERATOR %%(leftarg = text, rightarg = pg_trgm_match,
procedure=trgm_check_match);

ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD
   OPERATOR9   %% (text,
pg_trgm_match);

It does indeed make PostgreSQL complain about undefined strategy 9. I added
the following define to trgm.h:

#define ThresholdStrategyNumber 9

It seems StrategyNumber is used in gtrgm_consistent and gtrgm_distance.

In gtrgm_consistent, I need change the way `nlimit` is obtained:

nlimit = (strategy == SimilarityStrategyNumber) ?
similarity_threshold : word_similarity_threshold;

I need to add a case for ThresholdStrategyNumber and extract `nlimit` from
the argument of `pg_trgm_match`. I'm not sure what to do in
`gtrgm_distance`.

My questions:

1a. Is it possible to make `gtrgm_consistent` accept `text` or
`pg_trgm_match` as the second argument?
1b. What's the equivalent of `match.match` and `match.threshold` (where
`match` is a `pg_trgm_match`) in C?
2. What to do with `gtrgm_distance`?

Thanks for help.
-- 
Greg Navis
I help tech companies to scale Heroku-hosted Rails apps.
Free, biweekly scalability newsletter for SaaS CEOs

Re: [GENERAL] Why are no NEGATORS defined in the standard operators

[Daniel Migowski]

OK, I file a bug report. 

-Ursprüngliche Nachricht-
Von: Julien Rouhaud [mailto:julien.rouh...@dalibo.com] 
Gesendet: Samstag, 11. Juni 2016 11:28
An: Daniel Migowski ; pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Why are no NEGATORS defined in the standard operators

Hello

On 11/06/2016 10:00, Daniel Migowski wrote:
> Hello,
> 
> while trying to bake my own fixedpoint datatype I noticed the 
> definition for =(int8,int8) to be
> 
>  
> 
> CREATE OPERATOR =(
> 
>   PROCEDURE = int8eq,
> 
>   LEFTARG = int8,
> 
>   RIGHTARG = int8,
> 
>   COMMUTATOR = =,
> 
>   RESTRICT = eqsel,
> 
>   JOIN = eqjoinsel,
> 
>   HASHES,
> 
>   MERGES);
> 
>  
> 
> in PostgreSQL 9.5, but I wonder, why there is no NEGATOR clause 
> defined here? According to the docs it should help to add
> 
>  
> 
>   NEGATOR = <>
> 
>  
> 
> In query optimization. Is there some reason for it? Or is it a Bug in 
> pgAdmin III that the negator is not shown?
> 

I guess this is a bug in pgAdmin3, because the negator is defined in the
catalog:

# select o1.oprname, o1.oprcode, o1.oprnegate, o2.oprname as negate from 
pg_operator o1 join pg_operator o2 on o1.oprnegate = o2.oid where o1.oprname = 
'=' and o1.oprleft = 'int8'::regtype and o1.oprright = 'int8'::regtype;

 oprname │ oprcode │ oprnegate │ negate
═╪═╪═══╪
 =   │ int8eq  │   411 │ <>
(1 row)



--
Julien Rouhaud
http://dalibo.com - http://dalibo.org

-- 
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] Why are no NEGATORS defined in the standard operators

[Julien Rouhaud]

Hello

On 11/06/2016 10:00, Daniel Migowski wrote:
> Hello,
> 
> while trying to bake my own fixedpoint datatype I noticed the definition
> for =(int8,int8) to be
> 
>  
> 
> CREATE OPERATOR =(
> 
>   PROCEDURE = int8eq,
> 
>   LEFTARG = int8,
> 
>   RIGHTARG = int8,
> 
>   COMMUTATOR = =,
> 
>   RESTRICT = eqsel,
> 
>   JOIN = eqjoinsel,
> 
>   HASHES,
> 
>   MERGES);
> 
>  
> 
> in PostgreSQL 9.5, but I wonder, why there is no NEGATOR clause defined
> here? According to the docs it should help to add
> 
>  
> 
>   NEGATOR = <>
> 
>  
> 
> In query optimization. Is there some reason for it? Or is it a Bug in
> pgAdmin III that the negator is not shown?
> 

I guess this is a bug in pgAdmin3, because the negator is defined in the
catalog:

# select o1.oprname, o1.oprcode, o1.oprnegate, o2.oprname as negate from
pg_operator o1 join pg_operator o2 on o1.oprnegate = o2.oid where
o1.oprname = '=' and o1.oprleft = 'int8'::regtype and o1.oprright =
'int8'::regtype;

 oprname │ oprcode │ oprnegate │ negate
═╪═╪═══╪
 =   │ int8eq  │   411 │ <>
(1 row)



-- 
Julien Rouhaud
http://dalibo.com - http://dalibo.org


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

[GENERAL] pgAdmin 4 beta not working on Windows 10

[Thomas Kellerer]

Dave Page schrieb am 10.06.2016 um 16:48:

I'm pleased to announce that the release of pgAdmin 4 v1.0 Beta 1 for
testing. You can find more details on the website:

Announcement: https://www.pgadmin.org/

Documentation: https://www.pgadmin.org/docs4/dev/index.html

Downloads: https://www.pgadmin.org/download/

Bug tracker: https://redmine.postgresql.org/projects/pgadmin4/issues
(requires a PostgreSQL community login)



I don't have a community login, so I'm posting that here:

On Windows 10 64bit it doesn't start.

When running pgadmin4.exe I get a dialog

   "Fatal Error"
   An Error occurred initialising the application server:
Failed to launche the application server, server thread exiting.

Then it prompts me for a "Python Path" and an "Application Path".

I pointed that to

  c:\Program Files (x86)\pgAdmin 4\v1\runtime\python27.dll
  and
  c:\Program Files (x86)\pgAdmin 4\v1\runtime\pgAdmin4.exe

but after that, nothing happens.






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

[GENERAL] Why are no NEGATORS defined in the standard operators

[Daniel Migowski]

Hello,

while trying to bake my own fixedpoint datatype I noticed the definition for 
=(int8,int8) to be

CREATE OPERATOR =(
  PROCEDURE = int8eq,
  LEFTARG = int8,
  RIGHTARG = int8,
  COMMUTATOR = =,
  RESTRICT = eqsel,
  JOIN = eqjoinsel,
  HASHES,
  MERGES);

in PostgreSQL 9.5, but I wonder, why there is no NEGATOR clause defined here? 
According to the docs it should help to add

  NEGATOR = <>

In query optimization. Is there some reason for it? Or is it a Bug in pgAdmin 
III that the negator is not shown?

Regards,
Daniel Migowski

Re: [GENERAL] [HACKERS] Online DW

[Eduardo Morras]

On Sat, 11 Jun 2016 09:59:59 +0530
Sridhar N Bamandlapally  wrote:

> Ok, let me put this way,
> 
> I need every transaction coming from application sync with both
> production and archive db,
> but the transactions I do to clean old data(before 7 days) on
> production db in daily maintenance window should not sync with
> archive db,
> 
> Archive db need read-only, used for maintaining integrity with other
> business applications
> 
> Issue here is,
> 1. etl is scheduler, cannot run on every transaction, even if it
> does, its expensive
> 
> 2. Materialize view(refresh on commit) or slony, will also sync
> clean-up transactions
> 
> 3. Replication is not archive, definitely not option
> 
> I say, every online archive db is use case for this.

As I see, you have 2 options (A, and B)

A) With FDW

1) Create 2 databases on production: 
  a) first, a normal database for production, with 7 last days data,
  b) second, as postgres_fdw remote database on archive server.

https://www.postgresql.org/docs/current/static/postgres-fdw.html

2) Make a trigger on each table in production database to replicate
inserts, updates and deletes to fdw remote tables.

3) At your scheduled time, truncate tables (don't delete content or the
trigger will fire up) on your production db.


Note, make date part of your primary keys, if not, the same pk may be
reused on production tables and the insert on archive fdw tables will
fail.


B) Use pgpoolII, use replication to store the same data on both
databases. On production db, delete old data. Don't connect both
databases with streaming replication nor slony or anything else,
pgpoolII will do the work.

http://pgpool.net/mediawiki/index.php/Main_Page
http://www.pgpool.net/docs/latest/tutorial-en.html#replication-config

Read full documentation, above url cites point directly to the replication part 
but read full documentation.

HTH
 
> Thanks
> Sridhar
> Opentext


---   ---
Eduardo Morras 


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