Re: [GENERAL] Frequent "pg_ctl status" removing(?) semaphores (unlikely)

[Michael Paquier]

On Tue, Sep 27, 2016 at 2:13 PM, raf  wrote:
> So, my qestion is, is it possible that "pg_ctl status" could be
> removing postgres's semaphores and can I stop it? It seems
> extremely unlikely. So, if it isn't, what else could it be?
> Systemd perhaps? It's been known to kill screen/tmux/nohup
> processes when a user logs out in its keenness to clean up but
> that may be clutching at straws.

systemd sometimes has fun removing semaphores. See here for example:
https://www.postgresql.org/message-id/cak7teys9-o4bterbs3xuk2bffnnd55u2sm9j5r2fi7v6bhj...@mail.gmail.com
-- 
Michael


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

[GENERAL] lost synchronization with server: got message type "Z"

[Marek Petr]

Hello,



>From time to time we receive following event from application (Adobe 
Campaign - former Neolane):





PostgreSQL error: lost synchronization with server: got message type "Z", 
length 0\n (iRc=-2006)





Last time underlying sql statement was an update.




At the backend side there is:




LOG:  unexpected EOF on client connection with an open transaction





From http://doxygen.postgresql.org/fe-protocol3_8c_source.html#l00450 I can 
read:





  444 /*

  445  * handleSyncLoss: clean up after loss of message-boundary sync

  446  *

  447  * There isn't really a lot we can do here except abandon the 
connection.

  448  */





Could something else than network cause this event?




PostgreSQL version:





postgres=# select version();

                                                    version




 PostgreSQL 9.2.13 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 
20120313 (Red Hat 4.4.7-11), 64-bit

(1 row)








Thank you

Marek

Re: [GENERAL] Incrementally refreshed materialized view

[hariprasath nallasamy]

We also tried to achieve incremental refresh of materialized view and our
solution doesn't solve all of the use cases.

Players:
1) WAL
2) Logical decoding
3) replication slots
4) custom background worker

Two kinds of approaches :
1. Deferred refresh (oracle type of creating log table for each base tables
with its PK and agg's columns old and new values)
  a) Log table for each base table has to be created and this log table
will keep track of delta changes.
  b) UDF is called to refresh the view incrementally - this will
run original materialized view query with the tracked delta PK's in their
where clause. so only rows that are modified/inserted will be touched.
  c) Log table will keep track of changed rows from the data given by
replication slot which uses logical decoding to decode from WAL.
  d) Shared memory is used to maintain the relationship between the
view and its base table. In case of restart they are pushed to maintenance
table.

2. RealTime refresh (update the view whenever we get any change-sets
related to that base tables)
  a) Delta data from the replication slot will be applied to view by
checking the relationship between our delta data and the view definiton.
Here also shared memory and maintenance table are used.
  b) Work completed only for materialized views having single table.

Main disadvantage :
1) Data inconsistency when master failure and also slave doesn't have
replication slot as of now. But 2ndquard guys try to create slots in slave
using some concepts of failover slots. But that doesn't come along with PG
:(.
2) Sum, count and avg are implemented for aggregates(single table) and for
other aggs full refresh comes to play a role.
3) Right join implementation requires more queries to run on the top of
MV's.

So we are on a long way to go and dono whether this is the right path.

Only deferred refresh was pushed to github.
https://github.com/harry-2016/MV_IncrementalRefresh

I wrote a post regarding that in medium.
https://medium.com/@hariprasathnallsamy/postgresql-materialized-view-incremental-refresh-44d1ca742599

[GENERAL] Frequent "pg_ctl status" removing(?) semaphores (unlikely)

[raf]

Hi,

debian-8 (stable), postgres-9.4.9

I've just started running "/etc/init.d/postgresql-9.4 status"
every minute via cron and it seems to be having a very bad effect
on the server ["So stop doing it!" heard from the peanut gallery].

I noticed an error message like:

  FATAL:  the database system is in recovery mode

when it shouldn't have been in recovery mode,

and the log files say:

  [25844]: FATAL:  semctl(5505030, 6, SETVAL, 0) failed: Invalid argument
  [6708]: LOG:  server process (PID 25844) exited with exit code 1
  [6708]: LOG:  terminating any other active server processes
  [6714]: WARNING:  terminating connection because of crash of another server 
process
  [6714]: DETAIL:  The postmaster has commanded this server process to roll 
back the
 current transaction and exit, because another server 2016-09-27 10:27:16 
AEST::@:[6714]:
 HINT:  In a moment you should be able to reconnect to the database and 
repeat your command.
  [6708]: LOG:  all server processes terminated; reinitializing 
  [6708]: LOG:  could not remove shared memory segment 
"/PostgreSQL.1804289383": No such file or directory
  [6708]: LOG:  semctl(5308416, 0, IPC_RMID, ...) failed: Invalid argument
  [6708]: LOG:  semctl(5341185, 0, IPC_RMID, ...) failed: Invalid argument
  [6708]: LOG:  semctl(5373954, 0, IPC_RMID, ...) failed: Invalid argument
  [6708]: LOG:  semctl(5406723, 0, IPC_RMID, ...) failed: Invalid argument
  [6708]: LOG:  semctl(5439492, 0, IPC_RMID, ...) failed: Invalid argument
  [6708]: LOG:  semctl(5472261, 0, IPC_RMID, ...) failed: Invalid argument
  [6708]: LOG:  semctl(5505030, 0, IPC_RMID, ...) failed: Invalid argument
  [6708]: LOG:  semctl(5537799, 0, IPC_RMID, ...) failed: Invalid argument
  [25845]: LOG:  database system was interrupted; last known up at 2016-09-23 
09:29:28 AEST

However, it's not really in recovery mode and it doesn't come
good again until I manually restart the server.

Googling for this issue revealed that something is deleting
postgres's semaphores. Sure enough, the "ipcs -s" shows no
semaphores when this happens rather than showing the usual eight
lines of semaphore information.

The advice was to look for another script issuing ipcrm commands
(or, if running postgres in multiple freebsd jails, use a
different userid for each).

The only thing I'm doing differently since this started is
running this every minute from a script run by cron:

  /etc/init.d/postgresql-9.4 status

That means a call to:

  su -s /bin/sh - postgres -c 
"LD_LIBRARY_PATH=/opt/PostgreSQL/9.4/lib:$LD_LIBRARY_PATH
  /opt/PostgreSQL/9.4/bin/pgpg_ctl status -D \"/data/payroll-9.4\""

Now, I can't prove that "pg_ctl status" is causing the problem
but when I disable the cronjob, the problem disappears and
whenever I enable it the problem reappears fairly quickly (i.e.
within an hour or two) so I'm fairly convinced that it's
involved. And I've just noticed that the logfile messages
above do refer to IPC_RMID so some part of postgres
is trying to remove the semaphores but it looks like they're
already gone when it tries.

So, my qestion is, is it possible that "pg_ctl status" could be
removing postgres's semaphores and can I stop it? It seems
extremely unlikely. So, if it isn't, what else could it be?
Systemd perhaps? It's been known to kill screen/tmux/nohup
processes when a user logs out in its keenness to clean up but
that may be clutching at straws.

At first, when I saw this, I assumed that I had stopped the
server interactively at the same time as the cronjob was
starting it and the two actions clashed with regards to
semaphore creation and removal but I wasn't convinced. And I'm
not trying to stop the server now. I'm just running the cronjob
to check the status. And the problem still occurs.

In case you're wondering what else the cronjob does, the first
thing it does is:

  /etc/init.d/postgresql-9.4 status | grep -q 'server is running' && exit 0

So it's not doing anything else if postgres is running.

Any idea what I've done wrong? (apart from the obvious) :-)

cheers,
raf



-- 
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] Update two tables returning id from insert CTE Query

[Patrick B]

2016-09-27 16:22 GMT+13:00 Patrick B :

> Hi guys,
>
> I've got 2k rows in a table:
>
>> CREATE TABLE
>> public.not_monthly
>> (
>> id BIGINT DEFAULT "nextval"('"id_seq"'::"regclass") NOT NULL,
>> clientid BIGINT,
>> name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
>> name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
>> );
>
>
> I want to insert data from public.not_monthly to
> public.table_1(clientid,name_first) and public.table_2(client_id,c_id*(FROM
> table_1)*,name_last)
>
> table_2.c_id must have the ID from the insert on the table_1 table.
>
> I did this:
>
>> WITH rows AS (
>> SELECT
>>t1.id,
>> t1.clientid,
>> t1.name_first,
>> t1.name_last
>> row_number() OVER (ORDER BY t1.id) AS rn
>> FROM
>> public.not_monthly t1
>> ),
>> ins_table_1 AS (
>> INSERT INTO public.table_1 (clientid,name_first)
>> SELECT
>> clientid,
>> name_first
>> FROM rows
>> RETURNING id
>> ),
>> ins_table_2 AS (
>> INSERT INTO public.table_2 (name_last,clientid)
>> SELECT
>> name_last,
>> clientid
>> FROM rows
>> RETURNING id
>> )
>
>
> Then, I was able to select the table_1.id using:
>
>> SELECT i.id AS table_1_id, s.id AS not_monthly_id
>> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
>> JOIN rows s USING (rn)
>
>
> So I'd imagine now I would do the update? How can I update table_2.c_id
> with the ins_table_1.id value?
> I'm using Postgres 9.2
>
> Thanks
> Patrick
>


I'm doing this now:


sel AS (
> SELECT i.id AS c_id
> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
> JOIN rows s USING (rn)
> )
> UPDATE table_2 SET c_id =
> (
>   SELECT c_id
>   FROM sel
>   ORDER BY c_id
> )
> WHERE clientid = 124312;


But I get *ERROR:  more than one row returned by a subquery used as an
expression*

[GENERAL] Update two tables returning id from insert CTE Query

[Patrick B]

Hi guys,

I've got 2k rows in a table:

> CREATE TABLE
> public.not_monthly
> (
> id BIGINT DEFAULT "nextval"('"id_seq"'::"regclass") NOT NULL,
> clientid BIGINT,
> name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
> name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
> );


I want to insert data from public.not_monthly to
public.table_1(clientid,name_first) and public.table_2(client_id,c_id*(FROM
table_1)*,name_last)

table_2.c_id must have the ID from the insert on the table_1 table.

I did this:

> WITH rows AS (
> SELECT
>t1.id,
> t1.clientid,
> t1.name_first,
> t1.name_last
> row_number() OVER (ORDER BY t1.id) AS rn
> FROM
> public.not_monthly t1
> ),
> ins_table_1 AS (
> INSERT INTO public.table_1 (clientid,name_first)
> SELECT
> clientid,
> name_first
> FROM rows
> RETURNING id
> ),
> ins_table_2 AS (
> INSERT INTO public.table_2 (name_last,clientid)
> SELECT
> name_last,
> clientid
> FROM rows
> RETURNING id
> )


Then, I was able to select the table_1.id using:

> SELECT i.id AS table_1_id, s.id AS not_monthly_id
> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
> JOIN rows s USING (rn)


So I'd imagine now I would do the update? How can I update table_2.c_id
with the ins_table_1.id value?
I'm using Postgres 9.2

Thanks
Patrick

Re: [GENERAL] Replication slot on master failure

[Michael Paquier]

On Mon, Sep 26, 2016 at 7:49 PM, hariprasath nallasamy
 wrote:
>We are using replication slot for capturing some change sets to
> update dependent tables.
>Will there be inconsistency if the master fails and the standby takes
> the role of master.?

Replication slot creation is not replicated to standbys if that's what
you are looking for. So if you use a slot on master and consume its
data up to a given point, and then promote a standby, you may see a
gap of data after creating a slot, or if you created a slot previously
you may consume twice the same WAL records. Note that replication
slots created on standbys are initialized from the last checkpoint
redo record, so you could take advantage of this property before
promoting a standby.
-- 
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] Large pg_xlog

[Michael Paquier]

On Tue, Sep 27, 2016 at 9:26 AM, Francisco Reyes  wrote:
> The only times archive command failed I believe is because the volume where
> pg_xlog is ran out of space.
> FATAL:  archive command was terminated by signal 3: Quit
>
> There are 2 of those today. So that does not seem to be the cause.

What are the files in pg_xlog/archive_status? Do see a lot of .ready
entries? Perhaps you could de-bloat things by using archive_command =
'/bin/true' (REM on Windows as far as I recall).
-- 
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] Large pg_xlog

[Francisco Reyes]

On 09/26/2016 08:08 PM, Andreas Kretschmer wrote:
archive_command failed? If that happens the wal's will not deleted, 
you should see error-messages in the log. 



The only times archive command failed I believe is because the volume 
where pg_xlog is ran out of space.

FATAL:  archive command was terminated by signal 3: Quit

There are 2 of those today. So that does not seem to be the cause.


--
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] Large pg_xlog

[Andreas Kretschmer]

On 27 September 2016 01:52:26 CEST, Francisco Reyes  wrote:


>Any ideas why pg_xlog is going so high?

archive_command failed? If that happens the wal's will not deleted, you should 
see error-messages in the log.
-- 
Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.


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

[GENERAL] Large pg_xlog

[Francisco Reyes]

Postgresql 9.3

checkpoint_segments=6
wal_keep_segments=300


Machine is master for 2, asynchronous, slaves.


pg_xlog in /var/lib/postgresql/9.3/main/pg_xlog

NFS mount with WAL archiving in /opt/backups/walarchives/HostName


During a load of a file, using copy, the pg_xlog grew to almost 120GB 
and one of the slaves fell behind (at another data center). Since I am 
not using replication slots I expected WAL to be wall_keep_segments x 
16MB = 4GB. Any idea why it grew so large?



Once I rsync wal archives to the slave and it catches up then WAL goes down.


Any ideas why pg_xlog is going so high?



--
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] bitwise storage and operations

[David G. Johnston]

​
​Please include the list in all replies.

On Mon, Sep 26, 2016 at 4:14 PM, Jonathan Vanasco  wrote:

>
> On Sep 26, 2016, at 5:04 PM, David G. Johnston wrote:
>
> On Mon, Sep 26, 2016 at 1:44 PM, Jonathan Vanasco 
> wrote:
>
>> The documentation doesn't have any examples for SELECT for the bitwise
>> operators,
>
>
> That shows a simple computation.  One can "SELECT" any computation and get
> a value.
>
> It doesn't show a bitwise operator being used against an INT or BIT
> column, as I further elaborated.
>

I assumed a certain level of familiarity with databases and provided enough
info to answer your main question: "what are the available bit string
operators?".  That you can apply these operator to either constants or
columns was knowledge I took for granted.


> From what I can tell so far, i need to extract and compare a substring for
> the (reverse) index of the particular bit I want to filter on.
>

B'1001' is typed bit(4)...

The only requirement with a WHERE clause is that the computation must
result in a boolean.  My example SELECT computation does just that.  It
uses "varbit" for convenience but INT can be CAST() to BIT and the
operators themselves should operate on any of the BIT variants.

What you want is the "bit-wise AND" operator and the equality operator,
both of which you were shown.

I'd suggest you put forth your own example, filling in pseudo-code where
needed, if you wish for more specific advice.

David J.
​

Re: [GENERAL] bitwise storage and operations

[David G. Johnston]

On Mon, Sep 26, 2016 at 1:44 PM, Jonathan Vanasco  wrote:

> The documentation doesn't have any examples for SELECT for the bitwise
> operators,


Um...

​https://www.postgresql.org/docs/9.5/static/functions-bitstring.html​

SELECT B'111'::varbit & B'101'::varbit = B'101'::varbit

​SELECT 10::bit(8)::varbit​
​
​David J.​

[GENERAL] bitwise storage and operations

[Jonathan Vanasco]

We've been storing some "enumerated"/"set" data in postgresql as INT or BIT(32) 
for several years for some flags/toggles on records. 

This was preferable for storage to the ENUM type (or multiple columns), as we 
often changed the number of enumerated options or their labels -- and computing 
everything in the application saved the trouble of database migrations.  This 
has worked out perfectly -- until today.

For the first time ever, we need to run some queries that filter on these 
columns at the PostgreSQL level -- and I can't figure out how.

The documentation doesn't have any examples for SELECT for the bitwise 
operators, and everything I've found on various threads/forums has addressed 
inserts or converting on a select -- but never a comparison.

I've tried numerous forms and have gotten as far as CASTing everything to 
BIT(n), but I can't seem to construct a valid query that can filter what I want.

Can anyone share a sample WHERE clause or two that does a bitwise comparison 
against an INT or BIT column? 

Thanks!




-- 
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] Incrementally refreshed materialized view

[Kevin Grittner]

On Mon, Sep 26, 2016 at 3:16 PM, Adam Brusselback
 wrote:

> Well I feel like I've learned a ton already reading through the links you
> provided earlier and that example above.

Yeah, I know that example can really help show what will happen
"under the covers", and make it more concrete.  The theory that
it's based on seems brilliant to me.  That fact that it's based on
relational algebra (RA) means that it is much more likely to be
implemented in a complete and correct manner than something ad hoc.
 I know I started at this with a try (similar to yours) at
analyzing from scratch, then went and searched the literature.
When I came back to my ad hoc notes, the RA pointed out some holes
in the logic where corner cases would have been wrong because of
missed details.  RA is well thought out and solid; it seems to me
to be the perfect thing to underlie a solution to this problem.

> I'm very interested in getting this into core. I'll look into what I need to
> do to review. Not crazy familiar with C, as I mainly do Java development.
> I'll see if I can help in any way though.

Just testing it and reviewing the documentation for errors,
omissions, or just plain hard-to-follow language would be a big
help.  Please post back about any problems getting things patched
and build.

> The main reason I was working on an alternative is because I need something
> now rather than in a couple years, but I've been dealing with manually
> creating the few I do need for my database. What I proposed above was just
> me thinking about what could be done with things as they are. Obviously it's
> junk compared to a real solution in-core.  Would you consider my approach
> even worth trying, or should I just suck it up and do things manually for
> now and put that effort into getting incremental refresh into core?

Oh, I've used plain tables and triggers many times myself.  If you
need something now, you kind of have to go that route.  The
algorithms I cited do provide an interesting potential alternative
for how to go about that, although operating a row at a time you
probably won't approach the speed of statement-level set logic for
statements that affect very many rows.  :-(

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Incrementally refreshed materialized view

[Adam Brusselback]

On Mon, Sep 26, 2016 at 3:21 PM, Kevin Grittner  wrote:

> On Mon, Sep 26, 2016 at 2:04 PM, Rakesh Kumar
>  wrote:
>
> > Does PG have a concept of MV log, from where it can detect the
> > delta changes and apply  incremental changes quickly.
>
> That is what I am trying to work toward with the patch I cited in
> an earlier post.  Once some variation of that is in, the actual
> incremental maintenance can be build on top of it.  To see an
> example of what would be done with such a delta relation for a
> simple MV, using the count algorithm, see below:
>

Well I feel like I've learned a ton already reading through the links you
provided earlier and that example above.

I'm very interested in getting this into core. I'll look into what I need
to do to review. Not crazy familiar with C, as I mainly do Java
development. I'll see if I can help in any way though.

The main reason I was working on an alternative is because I need something
now rather than in a couple years, but I've been dealing with manually
creating the few I do need for my database. What I proposed above was just
me thinking about what could be done with things as they are. Obviously
it's junk compared to a real solution in-core.  Would you consider my
approach even worth trying, or should I just suck it up and do things
manually for now and put that effort into getting incremental refresh into
core?

Re: [GENERAL] inconsistent behaviour of set-returning functions in sub-query with random()

[Tom Lane]

Tom van Tilburg  writes:
> I'm often using the WHERE clause random() > 0.5 to pick a random subset of
> my data. Now I noticed that when using a set-returning function in a
> sub-query, I either get the whole set or none (meaning that the WHERE
> random() > 0.5 clause is interpreted *before* the set is being generated).
> e.g.:
>
> SELECT num FROM (
> SELECT unnest(Array[1,2,3,4,5,6,7,8,9,10]) num) AS foo WHERE random() > 
> 0.5;

Hmm, I think this is an optimizer bug.  There are two legitimate behaviors
here:

SELECT * FROM unnest(ARRAY[1,2,3,4,5,6,7,8,9,10]) WHERE random() > 0.5;

should (and does) re-evaluate the WHERE for every row output by unnest().

SELECT unnest(ARRAY[1,2,3,4,5,6,7,8,9,10]) WHERE random() > 0.5;

should evaluate WHERE only once, since that happens before expansion of the
set-returning function in the targetlist.  (If you're an Oracle user and
you imagine this query as having an implicit "FROM dual", the WHERE should
be evaluated for the single row coming out of the FROM clause.)

In the case you've got here, given the placement of the WHERE in the outer
query, you'd certainly expect it to be evaluated for each row coming out
of the inner query.  But the optimizer is deciding it can push the WHERE
clause down to become a WHERE of the sub-select.  That is legitimate in a
lot of cases, but not when there are SRF(s) in the sub-select's
targetlist, because that pushes the WHERE to occur before the SRF(s),
analogously to the change between the two queries I wrote.

I'm a bit hesitant to change this in existing releases.  Given the lack
of previous complaints, it seems more likely to break queries that were
behaving as-expected than to make people happy.  But we could change it
in v10 and up, especially since some other corner-case changes in
SRF-in-tlist behavior are afoot.

In the meantime, you could force it to work as you wish by inserting the
all-purpose optimization fence "OFFSET 0" in the sub-select:

=# SELECT num FROM (
SELECT unnest(Array[1,2,3,4,5,6,7,8,9,10]) num OFFSET 0) AS foo WHERE 
random() > 0.5;
 num 
-
   1
   4
   7
   9
(4 rows)


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] Incrementally refreshed materialized view

[Kevin Grittner]

On Mon, Sep 26, 2016 at 2:04 PM, Rakesh Kumar
 wrote:

> Does PG have a concept of MV log, from where it can detect the
> delta changes and apply  incremental changes quickly.

That is what I am trying to work toward with the patch I cited in
an earlier post.  Once some variation of that is in, the actual
incremental maintenance can be build on top of it.  To see an
example of what would be done with such a delta relation for a
simple MV, using the count algorithm, see below:


--
--
-- This file demonstrates how incremental maintenance of materialized views
-- is intended to work using the counting algorithm.  TEMP tables are used
-- to mock up both snapshots of relations and delta relations which will
-- be created automatically "behind the scenes" as part of the algorithm.
--
-- Delta relations for both base tables and materialized views will need a
-- "count(t)" column to track the number of alternative derivations of the
-- tuple.  The column is actually named that in this example.  Where the
-- count is needed by the algorithm and a relation doesn't have it
-- (because, for example, it is a base table), 1 is used.  This is kept in
-- the example for purposes of illustration.  While it is explicitly added
-- to the materialized views for this example, the idea would be that
-- specifying these materialized views using DISTINCT would implicitly add
-- the "count(t)" column when incremental maintenance was specified.
--
-- The logic for a JOIN is that the resulting row should have the product
-- of multiplying the two input row counts.  Calculating a delta for that
-- requires two such joins which are then processed by a UNION with special
-- semantics.  Those semantics are achieved in this example by feeding
-- UNION ALL results to an outer SELECT which uses GROUP BY and HAVING
-- clauses.
--
-- This example dodges some complex and performance-sapping problems that
-- occur when duplicate rows may be present.  It does so with a combination
-- of a PRIMARY KEY declaration in the base table and GROUP BY clauses in
-- the materialized views.
--
-- The temporary relation names in the example are chosen for clarity of
-- the example.  If temporary relation names are actually needed in the
-- implementation, they would need to be different, probably based on the
-- related permanent relation OID, for length.
--
--

-- Set up the base table used for these materialized views.
CREATE TABLE link
(
  src  text not null,
  dst  text not null,
  primary key (src, dst)
);
INSERT INTO link
  VALUES ('a','b'),('b','c'),('b','e'),('a','d'),('d','c');

-- Display and capture "before" image.
SELECT * FROM link ORDER BY 1, 2;
CREATE TEMP TABLE link1 AS SELECT * FROM link;

-- Create and display the initial state of the "hop" MV.
CREATE MATERIALIZED VIEW hop AS
  SELECT t1.src, t2.dst, count(*) AS "count(t)"
FROM link t1
JOIN link t2 ON (t2.src = t1.dst)
GROUP BY t1.src, t2.dst;
SELECT * FROM hop;

-- Execute a transaction that modifies the base table.
-- The assumption is that the changes will be applied shortly after commit.
BEGIN;
DELETE FROM link WHERE src = 'b' AND dst = 'e';
INSERT INTO link VALUES ('c','h'),('f','g');
COMMIT;

-- Display and capture "after" image.
SELECT * FROM link ORDER BY 1, 2;
CREATE TEMP TABLE link2 AS SELECT * FROM link;

-- Mock up the delta(link) table which will get created "behind the scenes".
CREATE TEMP TABLE "Δ(link)" (src text NOT NULL, dst text NOT NULL,
"count(t)" int NOT NULL);
INSERT INTO "Δ(link)" VALUES ('b','e',-1),('c','h',1),('f','g',1);
SELECT * FROM "Δ(link)" ORDER BY 1, 2;

-- Given link before and after images, and delta(link), calculate delta(hop).
-- This must happen while the "before" and "after" snapshots are still
registered,
-- but not necessarily in the same transaction as the modifications to
the base table.
CREATE TEMP TABLE "Δ(hop)"
  (src text NOT NULL, dst text NOT NULL, "count(t)" int NOT NULL);
INSERT INTO "Δ(hop)"
  SELECT src, dst, sum("count(t)") AS "count(t)"
FROM (
   SELECT delta.src, before.dst, delta."count(t)" * 1
 FROM "Δ(link)" delta
 JOIN link1 before ON (before.src = delta.dst)
   UNION ALL
   SELECT after.src, delta.dst, 1 * delta."count(t)"
 FROM link2 after
 JOIN "Δ(link)" delta ON (delta.src = after.dst)
 ) x(src, dst, "count(t)")
GROUP BY src, dst
HAVING sum("count(t)") <> 0;
SELECT * FROM "Δ(hop)" ORDER BY 1, 2;

-- Once the MV delta has been generated, the snapshots can be released.
-- We're using temp tables for demonstration purposes, so drop those,
-- and the base table's delta.
DROP TABLE link1, link2, "Δ(link)";

-- At some later time the MV delta is processed "behind the scenes".
-- We can't do the demonstration maintenance against the MV, so copy it.
CREATE 

Re: [GENERAL] Custom SQL function does not like IF-statement

[Alexander Farber]

Sorry, I've just realized you did that already.

And the WITH cte AS part is optional in this case...

Thank you

On Mon, Sep 26, 2016 at 9:12 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:

> Thank you Vik and others -
>
> On Mon, Sep 26, 2016 at 8:43 PM, Vik Fearing  wrote:
>
>> On 09/26/2016 08:22 PM, Alexander Farber wrote:
>> >
>> > CREATE OR REPLACE FUNCTION words_get_chat(
>> > in_uid integer,
>> > in_gid integer,
>> > in_msg varchar
>> > ) RETURNS TABLE (
>> > out_my boolean,
>> > out_msg varchar
>> > ) AS
>> > $func$
>> >
>> > IF LENGTH(TRIM(in_msg)) > 0 AND
>> > -- ensure only messages of player1 and player2 are
>> stored
>> > EXISTS (SELECT 1 FROM words_games
>> > WHERE gid = in_gid AND
>> > (player1 = in_uid OR player2 = in_uid)) THEN
>> >
>> > INSERT INTO words_chat (
>> > created,
>> > uid,
>> > gid,
>> > msg
>> > ) VALUES (
>> > CURRENT_TIMESTAMP,
>> > in_uid,
>> > in_gid,
>> > in_msg
>> > );
>> > END IF;
>> >
>> > SELECT
>> > uid = in_uid,
>> > msg
>> > FROM words_chat
>> > WHERE gid = in_gid
>> > ORDER BY created DESC;
>> >
>> > $func$ LANGUAGE sql;
>> >
>> > Unfortunately, PostgreSQL 9.5.4 does not like the syntax:
>> >
>> > ERROR:  syntax error at or near "IF"
>> > LINE 11: IF LENGTH(TRIM(in_msg)) > 0 AND
>> >  ^
>> >
>> > Please, how to rewrite my queries, so that the SQL function syntax is
>> ok?
>>
>> As others have said, IF is not SQL (at least not the dialect that
>> PostgreSQL understands).  You can rewrite the whole thing like this:
>>
>> WITH cte AS (
>> INSERT INTO words_chat (created, uid, gid, msg)
>> SELECT current_timestamp, in_uid, in_gid, in_msg
>> WHERE length(trim(in_msg)) > 0 AND
>>   EXISTS (SELECT 1 FROM words_games
>>   WHERE gid = in_gid AND
>> in_uid in (player1, player2))
>> )
>> SELECT uid = in_uid, msg
>> FROM words_chat
>> WHERE gid = in_gid
>> ORDER BY created DESC;
>>
>> > Is it maybe possible by adding a WHERE part to the UPDATE statement?
>>
>> Which UPDATE statement would that be?
>>
>
> Oops, I meant the INSERT.
>
> Could the both WHERE conditions be added there?
>
> Regards
> Alex
>

Re: [GENERAL] Custom SQL function does not like IF-statement

[Alexander Farber]

Thank you Vik and others -

On Mon, Sep 26, 2016 at 8:43 PM, Vik Fearing  wrote:

> On 09/26/2016 08:22 PM, Alexander Farber wrote:
> >
> > CREATE OR REPLACE FUNCTION words_get_chat(
> > in_uid integer,
> > in_gid integer,
> > in_msg varchar
> > ) RETURNS TABLE (
> > out_my boolean,
> > out_msg varchar
> > ) AS
> > $func$
> >
> > IF LENGTH(TRIM(in_msg)) > 0 AND
> > -- ensure only messages of player1 and player2 are stored
> > EXISTS (SELECT 1 FROM words_games
> > WHERE gid = in_gid AND
> > (player1 = in_uid OR player2 = in_uid)) THEN
> >
> > INSERT INTO words_chat (
> > created,
> > uid,
> > gid,
> > msg
> > ) VALUES (
> > CURRENT_TIMESTAMP,
> > in_uid,
> > in_gid,
> > in_msg
> > );
> > END IF;
> >
> > SELECT
> > uid = in_uid,
> > msg
> > FROM words_chat
> > WHERE gid = in_gid
> > ORDER BY created DESC;
> >
> > $func$ LANGUAGE sql;
> >
> > Unfortunately, PostgreSQL 9.5.4 does not like the syntax:
> >
> > ERROR:  syntax error at or near "IF"
> > LINE 11: IF LENGTH(TRIM(in_msg)) > 0 AND
> >  ^
> >
> > Please, how to rewrite my queries, so that the SQL function syntax is ok?
>
> As others have said, IF is not SQL (at least not the dialect that
> PostgreSQL understands).  You can rewrite the whole thing like this:
>
> WITH cte AS (
> INSERT INTO words_chat (created, uid, gid, msg)
> SELECT current_timestamp, in_uid, in_gid, in_msg
> WHERE length(trim(in_msg)) > 0 AND
>   EXISTS (SELECT 1 FROM words_games
>   WHERE gid = in_gid AND
> in_uid in (player1, player2))
> )
> SELECT uid = in_uid, msg
> FROM words_chat
> WHERE gid = in_gid
> ORDER BY created DESC;
>
> > Is it maybe possible by adding a WHERE part to the UPDATE statement?
>
> Which UPDATE statement would that be?
>

Oops, I meant the INSERT.

Could the both WHERE conditions be added there?

Regards
Alex

Re: [GENERAL] Incrementally refreshed materialized view

[Rakesh Kumar]

> Of course 9.5 is the current release so the answer is Yes, since 9.5

https://www.postgresql.org/docs/current/static/sql-creatematerializedview.html

What am I missing. I don't see any support for incremental refresh.

Just in case we are not speaking the same thing:  When a MV is created for the 
first time on a base table, all further updates on the base table, will be 
handled by only applying the delta changes to the base table. That is called 
incremental refresh. Check how DB2 does it:

http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r977.html

Or does " REFRESH MATERIALIZED VIEW." does it only incremental refresh.

Does PG have a concept of MV log, from where it can detect the delta changes 
and apply  incremental changes quickly.

Re: [GENERAL] Improving speed of query

[Nicolas Paris]

Hi,

You could run 2 queries separatly and asynchrouneously
1)  the limit 10
2)  the count

While the limit 10 query would be showned instanteneously, the web table
would way for the count to build the pagination

Le lun. 26 sept. 2016 à 20:59, Leonardo M. Ramé  a
écrit :

> Hi, I'm using a query to fill a paginated table. The task involves
> filtering, sorting, limit, offset and count of all rows (to determine
> the number of pages).
>
> My current query is this:
>
> select count(*) over() as totalrows,
>case when (d.filepath is not null) then '1' else '0' end as HasDocument,
>e.idtask, e.site, e.pacs, e.studyuid, e.accessionnumber,
>e.patientemail, e.refphysicianemail, e.sent, e.password, e.created,
>e.institutionname, e.patientname, e.studydate, e.studytime,
>e.proceduredescription, e.performingphysician, e.referringphysician,
>e.informantphysician, e.forcesend, e.sentdate, e.md5identifier,
>e.read, e.patientid
> from emailtasks e
> join sites s on s.identifier = e.site
> left join documents_current d on d.idtask=e.idtask
> where s.idsite = 34
> order by e.idtask desc
> limit 10 offset 0;
>
> I've made several indexes, and they really fast. The problem here is the
> window function count(*) to get the total number of rows.
>
> Here's the explain analyze result:
>
>   Limit  (cost=0.84..57.98 rows=10 width=310) (actual
> time=36075.589..36079.371 rows=10 loops=1)
> ->  WindowAgg  (cost=0.84..84302.61 rows=14754 width=310) (actual
> time=36075.581..36079.356 rows=10 loops=1)
>   ->  Nested Loop Left Join  (cost=0.84..84118.19 rows=14754
> width=310) (actual time=0.085..30639.311 rows=258839 loops=1)
> ->  Nested Loop  (cost=0.42..39977.25 rows=10170
> width=260) (actual time=0.071..10308.789 rows=146782 loops=1)
>   Join Filter: (e.site = s.identifier)
>   Rows Removed by Join Filter: 66794
>   ->  Index Scan using idx_emailtasks_idtask on
> emailtasks e  (cost=0.42..36772.35 rows=213576 width=260) (actual
> time=0.013..9929.527 rows=213576 loops=1)
>   ->  Materialize  (cost=0.00..1.27 rows=1 width=16)
> (actual time=0.000..0.001 rows=1 loops=213576)
> ->  Seq Scan on sites s (cost=0.00..1.26
> rows=1 width=16) (actual time=0.024..0.026 rows=1 loops=1)
>   Filter: (idsite = 34)
>   Rows Removed by Filter: 20
> ->  Index Scan using idx_documents_current_idtask on
> documents_current d (cost=0.42..4.32 rows=2 width=54) (actual
> time=0.092..0.136 rows=2 loops=146782)
>   Index Cond: (idtask = e.idtask)
>   Total runtime: 36106.813 ms
> (14 rows)
>
> What strategy do you recommend for speeding up this query?.
>
> Regards,
> --
> Leonardo M. Ramé
> Medical IT - Griensu S.A.
> Av. Colón 636 - Piso 8 Of. A
> X5000EPT -- Córdoba
> Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
> Cel.: +54 9 (011) 40871877
>
>
> --
> 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] need approval to join forums/community

[Adrian Klaver]

On 09/23/2016 10:26 PM, PHANIKUMAR G wrote:

hi owners/admins of postgresql,


my name is phani kumar and I would like to be member of postgres
forums/groups/communities. I have already sent mail regarding my
concern, looks my request overlooked and i did not get any response.
Second time I am approaching you, please add my mail id
phanikumar...@gmail.com  so that i can
be part of postgres knowledge sharing groups. As we are using postgres
in our products it is very important for me to be part of postgres
community.


So what are you signing up for:

Mailing Lists
https://www.postgresql.org/list/

or

Community account:

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

or both or something else?


Also, how did you sign up?




thanks
PhaniKumar



--
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] Incrementally refreshed materialized view

[Kevin Grittner]

On Mon, Sep 26, 2016 at 1:22 PM, Melvin Davidson  wrote:
> On Mon, Sep 26, 2016 at 1:56 PM, Adam Brusselback  
> wrote:
>>
>> I am working on a plan to implement incrementally refreshed
>> materialized "views" with the existing functionality in
>> Postgres.
>>
>> Below is the plan for doing that:

>> [design from scratch, incomplete]

>> I am however stuck on: How do we know what to refresh?

>> Pretty much, I need to figure out how to follow the joins in the
>> view back to whatever key was defined as the "refresh key" for
>> each dependent table.  I know about the
>> information_schema.view_column_usage, but I don't think that'll
>> get me everything I need.
>>
>> I'd really appreciate any help with this, as i'd love a better
>> way to get eagerly refreshed materialized views in Postgres
>> rather than doing everything manually as I have to now.
>>
>> If I can provide any more info please let me know.

> I am a bit curious. Why are you reinventing the wheel?
> What is wrong with:
>
> REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
> [ WITH [ NO ] DATA ]
>
> https://www.postgresql.org/docs/9.4/static/sql-refreshmaterializedview.html
>
> Can't you do that in a cron job?

Well, that is not *incremental* maintenance -- the entire query is
executed, with the resulting relation either replacing the previous
contents of the matview or "diffed" against the previous contents
(so that the difference can be applied with transactional
semantics), depending on whether CONCURRENTLY was specified.

The OP is still reinventing the wheel though.  A summary of
available techniques as of the mid-90s can be found here:

http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.40.2254=rep1=pdf

With some detail for what to me look like the two most promising
techniques here:

http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.31.3208=rep1=pdf

The first step in using either of those techniques (counting or
DRed) is to capture a delta relation to feed into the relational
algebra used by these techniques.  As a first step in that
direction I have been floating a patch to implement the
SQL-standard "transition tables" feature for AFTER triggers.

https://commitfest.postgresql.org/10/778/

If you want to help that effort, reading the thread and reviewing
the patch would be good.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Incrementally refreshed materialized view

[Adam Brusselback]

On Mon, Sep 26, 2016 at 2:35 PM, Rob Sargent  wrote:

> Of course 9.5 is the current release so the answer is Yes, since 9.5
>
> It seems like there is some confusion about what we're talking about. I am
talking about incremental updates to a sort of "fake" materialized view
(implemented as a table).  This is totally different than what we currently
have implemented for actual materialized views (REFRESH CONCURRENTLY).
Concurrent refresh just minimizes the time the view is locked by building a
second table in the background and merging the changes between them.

[GENERAL] Improving speed of query

[Leonardo M . Ramé]

Hi, I'm using a query to fill a paginated table. The task involves 
filtering, sorting, limit, offset and count of all rows (to determine 
the number of pages).


My current query is this:

select count(*) over() as totalrows,
  case when (d.filepath is not null) then '1' else '0' end as HasDocument,
  e.idtask, e.site, e.pacs, e.studyuid, e.accessionnumber,
  e.patientemail, e.refphysicianemail, e.sent, e.password, e.created,
  e.institutionname, e.patientname, e.studydate, e.studytime,
  e.proceduredescription, e.performingphysician, e.referringphysician,
  e.informantphysician, e.forcesend, e.sentdate, e.md5identifier,
  e.read, e.patientid
from emailtasks e
join sites s on s.identifier = e.site
left join documents_current d on d.idtask=e.idtask
where s.idsite = 34
order by e.idtask desc
limit 10 offset 0;

I've made several indexes, and they really fast. The problem here is the 
window function count(*) to get the total number of rows.


Here's the explain analyze result:

 Limit  (cost=0.84..57.98 rows=10 width=310) (actual 
time=36075.589..36079.371 rows=10 loops=1)
   ->  WindowAgg  (cost=0.84..84302.61 rows=14754 width=310) (actual 
time=36075.581..36079.356 rows=10 loops=1)
 ->  Nested Loop Left Join  (cost=0.84..84118.19 rows=14754 
width=310) (actual time=0.085..30639.311 rows=258839 loops=1)
   ->  Nested Loop  (cost=0.42..39977.25 rows=10170 
width=260) (actual time=0.071..10308.789 rows=146782 loops=1)

 Join Filter: (e.site = s.identifier)
 Rows Removed by Join Filter: 66794
 ->  Index Scan using idx_emailtasks_idtask on 
emailtasks e  (cost=0.42..36772.35 rows=213576 width=260) (actual 
time=0.013..9929.527 rows=213576 loops=1)
 ->  Materialize  (cost=0.00..1.27 rows=1 width=16) 
(actual time=0.000..0.001 rows=1 loops=213576)
   ->  Seq Scan on sites s (cost=0.00..1.26 
rows=1 width=16) (actual time=0.024..0.026 rows=1 loops=1)

 Filter: (idsite = 34)
 Rows Removed by Filter: 20
   ->  Index Scan using idx_documents_current_idtask on 
documents_current d (cost=0.42..4.32 rows=2 width=54) (actual 
time=0.092..0.136 rows=2 loops=146782)

 Index Cond: (idtask = e.idtask)
 Total runtime: 36106.813 ms
(14 rows)

What strategy do you recommend for speeding up this query?.

Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Incrementally refreshed materialized view

[Adam Brusselback]

I require eagerly refreshed materialized views for my use case, which is
something Postgres does not currently support.  I need my updates to a
table the view refers to visible within the same transaction, and often it
is a single change to one row which will only effect a single row in the
view.  If I used materialized views as they're currently implemented, that
would run the entire query and replace what needs to change, but it has to
run that entire query to do so.  For my use case, that is totally out of
the question to do for every insert / update / delete that could effect the
view.

For example, if I had a account balance materialized view that pretty much
summed transactions for a specific user from two different tables and
displayed a "balance" for every user, I would want that to only run the
query for the refresh for the specific user(s) that just had data
inserted/updated/deleted.  Not every user in the system after every
statement.

I've pretty much implemented this manually for some specific views which
performed horribly in Postgres (but would work fine in SQL Server for
example). I am looking to do this in a generic way so it's easier to
implement when necessary, and can be packaged as an extension for others
who may need to use it.

Obviously if we had better support for statement level triggers (so we
could reference all the NEW / OLD values touched by a statement) this would
be lower overhead, but that is one of the things holding up incrementally
refreshed materialized views from being implemented in the first place. I
just thought up a way to do it which gets around not having better
statement level triggers and wanted to see where I could get with things as
they are.

Re: [GENERAL] Custom SQL function does not like IF-statement

[Vik Fearing]

On 09/26/2016 08:22 PM, Alexander Farber wrote:
> Good evening!
> 
> For a 2-player game I am trying to create a custom SQL function, which
> stores a new message (if not empty) into words_chat table and then
> return all messages from that table for a given game:
> 
> CREATE OR REPLACE FUNCTION words_get_chat(
> in_uid integer,
> in_gid integer,
> in_msg varchar
> ) RETURNS TABLE (
> out_my boolean,
> out_msg varchar
> ) AS
> $func$
> 
> IF LENGTH(TRIM(in_msg)) > 0 AND
> -- ensure only messages of player1 and player2 are stored
> EXISTS (SELECT 1 FROM words_games 
> WHERE gid = in_gid AND 
> (player1 = in_uid OR player2 = in_uid)) THEN
> 
> INSERT INTO words_chat (
> created,
> uid,
> gid,
> msg
> ) VALUES (
> CURRENT_TIMESTAMP,
> in_uid,
> in_gid,
> in_msg
> );
> END IF;
> 
> SELECT 
> uid = in_uid,
> msg
> FROM words_chat
> WHERE gid = in_gid
> ORDER BY created DESC;
> 
> $func$ LANGUAGE sql;
> 
> Unfortunately, PostgreSQL 9.5.4 does not like the syntax:
> 
> ERROR:  syntax error at or near "IF"
> LINE 11: IF LENGTH(TRIM(in_msg)) > 0 AND
>  ^
> 
> Please, how to rewrite my queries, so that the SQL function syntax is ok?

As others have said, IF is not SQL (at least not the dialect that
PostgreSQL understands).  You can rewrite the whole thing like this:

WITH cte AS (
INSERT INTO words_chat (created, uid, gid, msg)
SELECT current_timestamp, in_uid, in_gid, in_msg
WHERE length(trim(in_msg)) > 0 AND
  EXISTS (SELECT 1 FROM words_games
  WHERE gid = in_gid AND
in_uid in (player1, player2))
)
SELECT uid = in_uid, msg
FROM words_chat
WHERE gid = in_gid
ORDER BY created DESC;

> Is it maybe possible by adding a WHERE part to the UPDATE statement?

Which UPDATE statement would that be?
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
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] Incrementally refreshed materialized view

[Rob Sargent]

Of course 9.5 is the current release so the answer is Yes, since 9.5

On 09/26/2016 12:29 PM, Rakesh Kumar wrote:


*Does PG support INCREMENTAL MV ? Looks like not (until 9.5)*

Re: [GENERAL] Incrementally refreshed materialized view

[Rakesh Kumar]

Does PG support INCREMENTAL MV ? Looks like not (until 9.5)

Re: [GENERAL] Custom SQL function does not like IF-statement

[hubert depesz lubaczewski]

On Mon, Sep 26, 2016 at 08:22:11PM +0200, Alexander Farber wrote:
> ERROR:  syntax error at or near "IF"
> LINE 11: IF LENGTH(TRIM(in_msg)) > 0 AND
>  ^

of course it doesn't like it, because sql doesn't have "if" command.

If you want to use such syntax, you have to use plpgsql language for the
function.

Best regards,

depesz



-- 
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] Custom SQL function does not like IF-statement

[Pavel Stehule]

Hi


2016-09-26 20:22 GMT+02:00 Alexander Farber :

> Good evening!
>
> For a 2-player game I am trying to create a custom SQL function, which
> stores a new message (if not empty) into words_chat table and then return
> all messages from that table for a given game:
>
> CREATE OR REPLACE FUNCTION words_get_chat(
> in_uid integer,
> in_gid integer,
> in_msg varchar
> ) RETURNS TABLE (
> out_my boolean,
> out_msg varchar
> ) AS
> $func$
>
> IF LENGTH(TRIM(in_msg)) > 0 AND
> -- ensure only messages of player1 and player2 are stored
> EXISTS (SELECT 1 FROM words_games
> WHERE gid = in_gid AND
> (player1 = in_uid OR player2 = in_uid)) THEN
>
> INSERT INTO words_chat (
> created,
> uid,
> gid,
> msg
> ) VALUES (
> CURRENT_TIMESTAMP,
> in_uid,
> in_gid,
> in_msg
> );
> END IF;
>
> SELECT
> uid = in_uid,
> msg
> FROM words_chat
> WHERE gid = in_gid
> ORDER BY created DESC;
>
> $func$ LANGUAGE sql;
>
> Unfortunately, PostgreSQL 9.5.4 does not like the syntax:
>
> ERROR:  syntax error at or near "IF"
> LINE 11: IF LENGTH(TRIM(in_msg)) > 0 AND
>
>
 ^
>
> Please, how to rewrite my queries, so that the SQL function syntax is ok?
>

SQL language doesn't support procedural statements like IF. You have to use
plpgsql.

Regards

Pavel


>
> Is it maybe possible by adding a WHERE part to the UPDATE statement?
>
> Greetings from Bochum
> Alex
>
>

Re: [GENERAL] Incrementally refreshed materialized view

[Melvin Davidson]

On Mon, Sep 26, 2016 at 1:56 PM, Adam Brusselback  wrote:

> Hello all,
> I am working on a plan to implement incrementally refreshed materialized
> "views" with the existing functionality in Postgres.
>
> Below is the plan for doing that:
>
> Trigger based eagerly updated materialized tables for Postgres 9.5
>>
>>
>>
>> High level plan:
>>
>> Have a view definition stored in the database which we can use for
>> reference.  Create functions which will read that view definition, and
>> create a materialized table with all the same columns as the reference
>> view, create triggers on all tables the view depends on to keep the
>> materialized table fresh within a transaction.  All queries would hit the
>> materialized table, the view is just there so we know what dependencies to
>> track, and have an easy way to update the materialized table.
>>
>>
>>
>> How do we actually do the refresh?
>>
>> 1.   A refresh key is defined for the materialized table.
>>
>> 2.   Every dependent table must roll up to that refresh key so we
>> know what rows to refresh.
>>
>> 3.   That key should be able to be referenced in the views where
>> clause performantly so we can refresh just the rows that match the refresh
>> key using the view.
>>
>> 4.   The refresh will be done by deleting any existing rows with the
>> key, and inserting new ones with the key from the view.
>>
>> How do we know what to refresh?
>>
>> 1.   A before statement trigger to create a temp table to log all
>> changes.
>>
>> 2.   A for each row trigger to log the rows modified by DML.
>>
>> a.   This should be done at the refresh key level.
>>
>>i.  We
>> need to figure out a way to generate queries to roll up things multiple
>> levels on the dependency chain until we get to the refresh key.  Not sure
>> at all how to do that.
>>
>> 3.   An after statement trigger to run a refresh on the materialized
>> table, looking at only the rows touched by the DML.
>>
>
> I am however stuck on: How do we know what to refresh?  -> Step 2
> Pretty much, I need to figure out how to follow the joins in the view back
> to whatever key was defined as the "refresh key" for each dependent table.
> I know about the information_schema.view_column_usage, but I don't think
> that'll get me everything I need.
>
> I'd really appreciate any help with this, as i'd love a better way to get
> eagerly refreshed materialized views in Postgres rather than doing
> everything manually as I have to now.
>
> If I can provide any more info please let me know.
> Thanks,
> -Adam
>


*I am a bit curious. Why are you reinventing the wheel?*

*What is wrong with:*

*REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
[ WITH [ NO ] DATA ]*



*https://www.postgresql.org/docs/9.4/static/sql-refreshmaterializedview.html
*



*Can't you do that in a cron job?*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Custom SQL function does not like IF-statement

[Alexander Farber]

Good evening!

For a 2-player game I am trying to create a custom SQL function, which
stores a new message (if not empty) into words_chat table and then return
all messages from that table for a given game:

CREATE OR REPLACE FUNCTION words_get_chat(
in_uid integer,
in_gid integer,
in_msg varchar
) RETURNS TABLE (
out_my boolean,
out_msg varchar
) AS
$func$

IF LENGTH(TRIM(in_msg)) > 0 AND
-- ensure only messages of player1 and player2 are stored
EXISTS (SELECT 1 FROM words_games
WHERE gid = in_gid AND
(player1 = in_uid OR player2 = in_uid)) THEN

INSERT INTO words_chat (
created,
uid,
gid,
msg
) VALUES (
CURRENT_TIMESTAMP,
in_uid,
in_gid,
in_msg
);
END IF;

SELECT
uid = in_uid,
msg
FROM words_chat
WHERE gid = in_gid
ORDER BY created DESC;

$func$ LANGUAGE sql;

Unfortunately, PostgreSQL 9.5.4 does not like the syntax:

ERROR:  syntax error at or near "IF"
LINE 11: IF LENGTH(TRIM(in_msg)) > 0 AND
 ^

Please, how to rewrite my queries, so that the SQL function syntax is ok?

Is it maybe possible by adding a WHERE part to the UPDATE statement?

Greetings from Bochum
Alex

[GENERAL] Incrementally refreshed materialized view

[Adam Brusselback]

Hello all,
I am working on a plan to implement incrementally refreshed materialized
"views" with the existing functionality in Postgres.

Below is the plan for doing that:

Trigger based eagerly updated materialized tables for Postgres 9.5
>
>
>
> High level plan:
>
> Have a view definition stored in the database which we can use for
> reference.  Create functions which will read that view definition, and
> create a materialized table with all the same columns as the reference
> view, create triggers on all tables the view depends on to keep the
> materialized table fresh within a transaction.  All queries would hit the
> materialized table, the view is just there so we know what dependencies to
> track, and have an easy way to update the materialized table.
>
>
>
> How do we actually do the refresh?
>
> 1.   A refresh key is defined for the materialized table.
>
> 2.   Every dependent table must roll up to that refresh key so we
> know what rows to refresh.
>
> 3.   That key should be able to be referenced in the views where
> clause performantly so we can refresh just the rows that match the refresh
> key using the view.
>
> 4.   The refresh will be done by deleting any existing rows with the
> key, and inserting new ones with the key from the view.
>
> How do we know what to refresh?
>
> 1.   A before statement trigger to create a temp table to log all
> changes.
>
> 2.   A for each row trigger to log the rows modified by DML.
>
> a.   This should be done at the refresh key level.
>
>i.  We
> need to figure out a way to generate queries to roll up things multiple
> levels on the dependency chain until we get to the refresh key.  Not sure
> at all how to do that.
>
> 3.   An after statement trigger to run a refresh on the materialized
> table, looking at only the rows touched by the DML.
>

I am however stuck on: How do we know what to refresh?  -> Step 2
Pretty much, I need to figure out how to follow the joins in the view back
to whatever key was defined as the "refresh key" for each dependent table.
I know about the information_schema.view_column_usage, but I don't think
that'll get me everything I need.

I'd really appreciate any help with this, as i'd love a better way to get
eagerly refreshed materialized views in Postgres rather than doing
everything manually as I have to now.

If I can provide any more info please let me know.
Thanks,
-Adam

[GENERAL] need approval to join forums/community

[PHANIKUMAR G]

hi owners/admins of postgresql,


my name is phani kumar and I would like to be member of postgres
forums/groups/communities. I have already sent mail regarding my concern,
looks my request overlooked and i did not get any response. Second time I
am approaching you, please add my mail id phanikumar...@gmail.com so that i
can be part of postgres knowledge sharing groups. As we are using postgres
in our products it is very important for me to be part of postgres
community.


thanks
PhaniKumar

[GENERAL] inconsistent behaviour of set-returning functions in sub-query with random()

[Tom van Tilburg]

Hi List,

Note beforehand: this question is a result of a stack-exchange that can be
seen here:
http://stackoverflow.com/questions/39624241/inconsistent-behaviour-of-set-returning-functions-in-sub-query-with-random

I'm often using the WHERE clause random() > 0.5 to pick a random subset of
my data. Now I noticed that when using a set-returning function in a
sub-query, I either get the whole set or none (meaning that the WHERE
random() > 0.5 clause is interpreted *before* the set is being generated).
e.g.:

SELECT num FROM (
SELECT unnest(Array[1,2,3,4,5,6,7,8,9,10]) num) AS foo WHERE random() > 0.5;

This seems inconsistent because the following query *does* take the whole
set into account:

SELECT num FROM (
SELECT * FROM unnest(Array[1,2,3,4,5,6,7,8,9,10]) num) AS foo
WHERE random() > 0.5;

So does this one:

WITH foo AS (
SELECT unnest(Array[1,2,3,4,5,6,7,8,9,10]) num)
SELECT num FROM foo WHERE random() > 0.5;

Could anyone reflect on the seeming inconsistency here? I do understand
that the planner sees the queries quite different (as can be seen from an
EXPLAIN) but I don't understand the rationale behind it.

Notes:

   -

   couldn't find another function to test apart from random(), but likely
   there is some
   -

   I tested with generate_series and as well
   -

   My real use case works with postgis and pgpointcloud where a range of
   set-returning functions is used in this manner

Thanks,
 Tom

RES: [GENERAL] Chante domain type - Postgres 9.2

[Márcio A . Sepp]

> > Can you elaborate?  Why would anyone create a text column to store
> customer name or product name which can very well be in varchar(50)
> type of cols.
> 
> You sound like you think that varchar(50) is somehow cheaper than text.
> That's backwards (at least in PG, other DBMSes may be different).
> There's no advantage storage-wise, and there is a cost, namely the cost
> of applying the length check on every update.
> 
> If you feel that you must have a check for application-specific
> reasons, then sure, use varchar(n).  But the number had better be one
> that you can trace to crystal-clear application requirements.
> varchar(n) where n has been plucked from the air is a good sign of bad
> database design.

What a about using text x varchar(n) in primary key? 




-- 
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] [HACKERS] temporary table vs array performance

[David G. Johnston]

On Mon, Sep 26, 2016 at 9:18 AM, 邓彪  wrote:

> we have to do dml in temp table,the CTE is not fit
>
>
​Moving this to -general only...​

​Please direct all replies to the list.

You are asking for help but not providing any context for what your
requirements are.  You are not likely to get good help.

Best case, supply a working function (self contained test case) that does
exactly what you need it to do but uses a temporary table and performs
badly.  Lacking that at least attempt to describe your problem and not just
point out that creating temporary tables is expensive.

David J.
​

Re: [GENERAL] [HACKERS] temporary table vs array performance

[Pavel Stehule]

2016-09-26 17:39 GMT+02:00 dby...@163.com :

> test:
> create type  h3 as (id int,name char(10));
>
> CREATE or replace FUNCTION proc17()
> RETURNS SETOF h3  AS $$
> DECLARE
> v_rec h3;
> BEGIN
> create temp table abc(id int,name varchar) on commit drop;
> insert into abc select 1,'lw';
> insert into abc select 2,'lw2';
> for v_rec in
> select * from abc loop
> return next v_rec;
> end loop;
> END;
> $$
> LANGUAGE plpgsql;
>
>
> CREATE or replace FUNCTION proc16()
> RETURNS   SETOF h3 AS $$
> DECLARE
>  id_array int[];
>  name_arr varchar[];
>  v_rec h3;
> BEGIN
> id_array =array[1,2];
> name_arr=array['lw','lw2'];
> for v_rec in
> select unnest(id_array)  ,unnest(name_arr) loop
> return next v_rec;
> end loop;
> END;
> $$
> LANGUAGE plpgsql;
> postgres=# select * from proc17();
>  id |name
> +
>   1 | lw
>   2 | lw2
> (2 rows)
>
> Time: 68.372 ms
> postgres=# select * from proc16();
>  id |name
> +
>   1 | lw
>   2 | lw2
> (2 rows)
>
> Time: 1.357 ms
>
> temp talbe result:
> [postgres@pg95 test_sql]$ pgbench -M prepared -n -r -c
> 2 -j 2 -T 10 -f temporary_test_1.sql
> transaction type: Custom query
> scaling factor: 1
> query mode: prepared
> number of clients: 2
> number of threads: 2
> duration: 10 s
> number of transactions actually processed: 5173
> latency average: 3.866 ms
> tps = 517.229191 (including connections establishing)
> tps = 517.367956 (excluding connections establishing)
> statement latencies in milliseconds:
> 3.863798 select * from proc17();
>
> array result:
> [postgres@pg95 test_sql]$ pgbench -M prepared -n -r -c
> 2 -j 2 -T 10 -f arrary_test_1.sql
> transaction type: Custom query
> scaling factor: 1
> query mode: prepared
> number of clients: 2
> number of threads: 2
> duration: 10 s
> number of transactions actually processed: 149381
> latency average: 0.134 ms
> tps = 14936.875176 (including connections establishing)
> tps = 14940.234960 (excluding connections establishing)
> statement latencies in milliseconds:
> 0.132983 select * from proc16();
>
> Array is not convenient to use in function, whether
> there are other methods can be replaced temp table in function
>
>
Temporary tables are pretty expensive - from more reasons, and horrible
when you use fresh table for two rows only. More if you recreate it every
transaction.

More often pattern is create first and delete repeatedly. Better don't use
temp tables when it is necessary. It is one reason why PostgreSQL supports
a arrays. Partially - PostgreSQL arrays are analogy to T-SQL memory tables.

Regards

Pavel




>
> --
> dby...@163.com
>

Re: [GENERAL] [HACKERS] temporary table vs array performance

[David G. Johnston]

Its considered bad form to post to multiple lists.  Please pick the most
relevant one - in this case I'd suggest -general.

On Mon, Sep 26, 2016 at 8:39 AM, dby...@163.com  wrote:

>
> Array is not convenient to use in function, whether
> there are other methods can be replaced temp table in function
>
>
​I have no difficulty using arrays in functions.

As for "other methods" - you can use CTE (WITH) to create a truly local
table - updating the catalogs by using a temp table is indeed quite
expensive.

WITH vals AS  ( VALUES (1, 'lw'), (2, 'lw2') )
SELECT * FROM vals;

David J.

[GENERAL] temporary table vs array performance

[dby...@163.com]

test:
create type  h3 as (id int,name char(10));

CREATE or replace FUNCTION proc17() 
RETURNS SETOF h3  AS $$ 
DECLARE
v_rec h3;
BEGIN 
create temp table abc(id int,name varchar) on commit drop;
insert into abc select 1,'lw';
insert into abc select 2,'lw2';
for v_rec in
select * from abc loop
return next v_rec;
end loop;
END; 
$$ 
LANGUAGE plpgsql;


CREATE or replace FUNCTION proc16() 
RETURNS   SETOF h3 AS $$ 
DECLARE
 id_array int[];
 name_arr varchar[];
 v_rec h3;
BEGIN 
id_array =array[1,2];
name_arr=array['lw','lw2'];
for v_rec in
select unnest(id_array)  ,unnest(name_arr) loop
return next v_rec;
end loop;
END; 
$$ 
LANGUAGE plpgsql;
postgres=# select * from proc17();
 id |name
+
  1 | lw
  2 | lw2   
(2 rows)

Time: 68.372 ms
postgres=# select * from proc16();
 id |name
+
  1 | lw
  2 | lw2   
(2 rows)

Time: 1.357 ms

temp talbe result:
[postgres@pg95 test_sql]$ pgbench -M prepared -n -r -c 2 -j 2 -T 10 -f 
temporary_test_1.sql 
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 2
number of threads: 2
duration: 10 s
number of transactions actually processed: 5173
latency average: 3.866 ms
tps = 517.229191 (including connections establishing)
tps = 517.367956 (excluding connections establishing)
statement latencies in milliseconds:
3.863798 select * from proc17();

array result:
[postgres@pg95 test_sql]$ pgbench -M prepared -n -r -c 2 -j 2 -T 10 -f 
arrary_test_1.sql 
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 2
number of threads: 2
duration: 10 s
number of transactions actually processed: 149381
latency average: 0.134 ms
tps = 14936.875176 (including connections establishing)
tps = 14940.234960 (excluding connections establishing)
statement latencies in milliseconds:
0.132983 select * from proc16();

Array is not convenient to use in function, whether there are other methods can 
be replaced temp table in function




dby...@163.com

Re: [GENERAL] Stats update difference between VACUUM ANALYZE and ANALYZE in 9.2?

[pbj]

On Monday, September 26, 2016 9:44 AM, Tom Lane  wrote:

 >> Paul Jones  writes:
 >> For a freshly pg_restore'd 9.2 database, would VACUUM ANALYZE update
 >> statistics any better than just an ANALYZE?

 >
 > VACUUM would have caused the page-all-visible flags to get set for all
 > pages of unchanging tables.  I don't recall whether ANALYZE has any side
 > effects on those flags at all, but it certainly would not have set them
 > for pages it didn't even visit, which would be most.
 >
 > Net result is that the pg_class.relallvisible fractions didn't get high
 > enough to persuade the planner that index-only scans would be effective.
 > I guess you could call that a statistic, but it's really about the
 > contents of the tables' free space maps.
  
 > 
 > regards, tom lane

This is good to know.  I think we will be running VACUUM ANALYZE from 
now on after restore instead of just ANALYZE.
 
I do note that sect. 49.11 claims that ANALYZE updates 
pg_class.relallvisible.  I don't know if this is a documentation problem 
in light of what you explained.

PJ

Re: [GENERAL] Chante domain type - Postgres 9.2

[Tom Lane]

Rob Sargent  writes:
> On 09/26/2016 08:14 AM, Adrian Klaver wrote:
>> https://www.postgresql.org/docs/9.5/static/datatype-character.html
>> ".. If character varying is used without length specifier, the type 
>> accepts strings of any size. The latter is a PostgreSQL extension."

> Does that trick remove the overhead (length check) Tom mentioned upstream?

Partly.  It should get rid of actual calls to the varchar length checking
function.  There's still some distributed overhead arising from the fact
that text, not varchar, is the native string type in Postgres.  So for
example anyplace you apply a concatenation operator, varchar inputs have
to be casted to text, and the result has to be casted to varchar if it's
being stored into a varchar field.  I've never seen any serious attempt
to quantify how much that costs, but it's not zero.

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] Chante domain type - Postgres 9.2

[Adrian Klaver]

On 09/26/2016 07:38 AM, Rob Sargent wrote:



On 09/26/2016 08:14 AM, Adrian Klaver wrote:

On 09/26/2016 06:54 AM, Thomas Kellerer wrote:

Rakesh Kumar schrieb am 26.09.2016 um 15:08:

You sound like you think that varchar(50) is somehow cheaper than
text.


The biggest impediment to text cols in other RDBMS  is no index
allowed.
If PG has an elegant solution to that, then yes I see the point made
by the
original poster.


Don't confuse Postgres' "text" data type with "text" in other DBMS.


Just be aware that layers above the database often do not understand
that and will see text as a memo field. For instance in Django a text
field will get rendered as a Textarea widget whereas a varchar field
will be rendered as an TextInput widget. You can override that, but it
is extra work. Luckily Postgres has the notion of an unbounded varchar:

https://www.postgresql.org/docs/9.5/static/datatype-character.html

".. If character varying is used without length specifier, the type
accepts strings of any size. The latter is a PostgreSQL extension."

This allows you to have 'text' without it being called text, as stated
below.



There is no difference whatsoever between varchar and text in Postgres.



Does that trick remove the overhead (length check) Tom mentioned upstream?


Should have said earlier, the other side of the story is it makes your 
schema less portable. Since I gave up on that some time ago it is not 
something that is my first concern.




--
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] Chante domain type - Postgres 9.2

[Adrian Klaver]

On 09/26/2016 07:38 AM, Rob Sargent wrote:



On 09/26/2016 08:14 AM, Adrian Klaver wrote:

On 09/26/2016 06:54 AM, Thomas Kellerer wrote:

Rakesh Kumar schrieb am 26.09.2016 um 15:08:

You sound like you think that varchar(50) is somehow cheaper than
text.


The biggest impediment to text cols in other RDBMS  is no index
allowed.
If PG has an elegant solution to that, then yes I see the point made
by the
original poster.


Don't confuse Postgres' "text" data type with "text" in other DBMS.


Just be aware that layers above the database often do not understand
that and will see text as a memo field. For instance in Django a text
field will get rendered as a Textarea widget whereas a varchar field
will be rendered as an TextInput widget. You can override that, but it
is extra work. Luckily Postgres has the notion of an unbounded varchar:

https://www.postgresql.org/docs/9.5/static/datatype-character.html

".. If character varying is used without length specifier, the type
accepts strings of any size. The latter is a PostgreSQL extension."

This allows you to have 'text' without it being called text, as stated
below.



There is no difference whatsoever between varchar and text in Postgres.











Does that trick remove the overhead (length check) Tom mentioned upstream?



I believe so if I am reading the docs right:

https://www.postgresql.org/docs/9.5/static/datatype-character.html

"(The maximum value that will be allowed for n in the data type 
declaration is less than that. It wouldn't be useful to change this 
because with multibyte character encodings the number of characters and 
bytes can be quite different. If you desire to store long strings with 
no specific upper limit, use text or character varying without a length 
specifier, rather than making up an arbitrary length limit.)


Tip: There is no performance difference among these three types, apart 
from increased storage space when using the blank-padded type, and a few 
extra CPU cycles to check the length when storing into a 
length-constrained column. While character(n) has performance advantages 
in some other database systems, there is no such advantage in 
PostgreSQL; in fact character(n) is usually the slowest of the three 
because of its additional storage costs and slower sorting. In most 
situations text or character varying should be used instead."



--
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] Chante domain type - Postgres 9.2

[Rob Sargent]

On 09/26/2016 08:14 AM, Adrian Klaver wrote:

On 09/26/2016 06:54 AM, Thomas Kellerer wrote:

Rakesh Kumar schrieb am 26.09.2016 um 15:08:
You sound like you think that varchar(50) is somehow cheaper than 
text.


The biggest impediment to text cols in other RDBMS  is no index 
allowed.
If PG has an elegant solution to that, then yes I see the point made 
by the

original poster.


Don't confuse Postgres' "text" data type with "text" in other DBMS.


Just be aware that layers above the database often do not understand 
that and will see text as a memo field. For instance in Django a text 
field will get rendered as a Textarea widget whereas a varchar field 
will be rendered as an TextInput widget. You can override that, but it 
is extra work. Luckily Postgres has the notion of an unbounded varchar:


https://www.postgresql.org/docs/9.5/static/datatype-character.html

".. If character varying is used without length specifier, the type 
accepts strings of any size. The latter is a PostgreSQL extension."


This allows you to have 'text' without it being called text, as stated 
below.




There is no difference whatsoever between varchar and text in Postgres.











Does that trick remove the overhead (length check) Tom mentioned upstream?




--
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] Chante domain type - Postgres 9.2

[Adrian Klaver]

On 09/26/2016 06:54 AM, Thomas Kellerer wrote:

Rakesh Kumar schrieb am 26.09.2016 um 15:08:

You sound like you think that varchar(50) is somehow cheaper than text.


The biggest impediment to text cols in other RDBMS  is no index allowed.
If PG has an elegant solution to that, then yes I see the point made by the
original poster.


Don't confuse Postgres' "text" data type with "text" in other DBMS.


Just be aware that layers above the database often do not understand 
that and will see text as a memo field. For instance in Django a text 
field will get rendered as a Textarea widget whereas a varchar field 
will be rendered as an TextInput widget. You can override that, but it 
is extra work. Luckily Postgres has the notion of an unbounded varchar:


https://www.postgresql.org/docs/9.5/static/datatype-character.html

".. If character varying is used without length specifier, the type 
accepts strings of any size. The latter is a PostgreSQL extension."


This allows you to have 'text' without it being called text, as stated 
below.




There is no difference whatsoever between varchar and text in Postgres.










--
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] Chante domain type - Postgres 9.2

[Thomas Kellerer]

Rakesh Kumar schrieb am 26.09.2016 um 15:08:
>>You sound like you think that varchar(50) is somehow cheaper than text.
> 
> The biggest impediment to text cols in other RDBMS  is no index allowed. 
> If PG has an elegant solution to that, then yes I see the point made by the
> original poster.

Don't confuse Postgres' "text" data type with "text" in other DBMS. 

There is no difference whatsoever between varchar and text in Postgres. 







-- 
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] Stats update difference between VACUUM ANALYZE and ANALYZE in 9.2?

[Tom Lane]

Paul Jones  writes:
> For a freshly pg_restore'd 9.2 database, would VACUUM ANALYZE update
> statistics any better than just an ANALYZE?

Not as far as the statistics kept in pg_stat go.

> After a restore, we ran a bunch of ANALYZEs on each table individually
> using GNU 'parallel' (for speed).  Many of these tables are child tables
> in a partition.  Following the ANALYZEs, a join with the parent table
> showed all of the child tables scanned sequentially.

> After running VACUUM ANALYZE on the whole database, the same join used
> index-only scans on the child tables.

VACUUM would have caused the page-all-visible flags to get set for all
pages of unchanging tables.  I don't recall whether ANALYZE has any side
effects on those flags at all, but it certainly would not have set them
for pages it didn't even visit, which would be most.

Net result is that the pg_class.relallvisible fractions didn't get high
enough to persuade the planner that index-only scans would be effective.
I guess you could call that a statistic, but it's really about the
contents of the tables' free space maps.

Another possible theory is that you skipped ANALYZE'ing the partition
parent tables in your first pass, but I'm betting on the all-visible
fractions as being the issue.

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] Stats update difference between VACUUM ANALYZE and ANALYZE in 9.2?

[Paul Jones]

For a freshly pg_restore'd 9.2 database, would VACUUM ANALYZE update
statistics any better than just an ANALYZE?

After a restore, we ran a bunch of ANALYZEs on each table individually
using GNU 'parallel' (for speed).  Many of these tables are child tables
in a partition.  Following the ANALYZEs, a join with the parent table
showed all of the child tables scanned sequentially.

After running VACUUM ANALYZE on the whole database, the same join used
index-only scans on the child tables.

An examination of the fine manual implies there may be some difference
(or a documentation conflict?) between running ANALYZE manually on
individual tables and an unqualified ANALYZE on the whole database.

5.9.6:
"If you are using manual VACUUM or ANALYZE commands, don't forget
that you need to run them on each partition individually. A
command like:
ANALYZE measurement;
will only process the master table."

ANALYZE:
"If the table being analyzed has one or more children, ANALYZE
will gather statistics twice: once on the rows of the parent table
only, and a second time on the rows of the parent table with all
of its children. This second set of statistics is needed when
planning queries that traverse the entire inheritance tree. The
autovacuum daemon, however, will only consider inserts or updates
on the parent table itself when deciding whether to trigger
an automatic analyze for that table. If that table is rarely
inserted into or updated, the inheritance statistics will not
be up to date unless you run ANALYZE manually."

Can anyone explain what's going on here?

Thanks,
Paul Jones


-- 
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] Chante domain type - Postgres 9.2

[Michael Sheaver]

TEXT is a native type in PostgreSQL, and is highly optimized behind the scenes 
to be as fast and efficient as possible in both the storage and retrieval of 
the data.

Regarding user input validation, it is almost always better to let the 
customer-facing app do the validation instead of relying upon the backend 
storage engine to do this for you. One reason for this it isn't trivial to 
capture the error from PostgreSQL, parse it and present it to the user in an 
intelligible manner. Second, it is usually much easier to change the validation 
in the user interface than to do it in the backend database.

For example, if you have a user requirement to limit say, LastName to 50 
characters, and years down the road you discover that you need to up it to 75 
characters, you can change it in the interface and be done with it. But if you 
had originally used VARCHAR(50) in the backend, you will need to change it to 
VARCHAR(75) in every table where you use LastName. I have had to do this 
myself, and believe me, it is not fun.

As fellow DBAs and devs who have had these kinds of painful experiences, we are 
just trying to save you from the same pitfalls. But I guess that there is some 
truth to the old adage that we must learn from our own mistakes. :)


> On Sep 26, 2016, at 8:46 AM, Michael Sheaver  wrote:
> 
> I have done some research after converting my database from MySQL 5.6 to 
> PostgreSQL 9.6 (the best move I have ever made), and the consensus I found 
> can be summed up as:
> 1.  Never, neve, never use VARCHAR or even CHAR
> 2. Always always, always use TEXT
> 
> Unless, that is, you have some kind of edge case. This may require a little 
> work upfront, but it will save you from a TON of grief down the road.
> 
> 
>> On Sep 26, 2016, at 8:29 AM, Jan de Visser > > wrote:
>> 
>> On 2016-09-26 1:15 AM, Gavin Flower wrote:
>> 
>>> On 26/09/16 17:58, Patrick B wrote:
 Hi guys,
 
 I've got this domain:
 
CREATE DOMAIN public.a_city
  AS character varying(80)
  COLLATE pg_catalog."default";
 
 
 And I need to increase the type from character varying(80) to character 
 varying(255).
 
 How can I do that? didn't find info about it. I'm using Postgres 9.2
 
 Thanks!
 Patrick
>>> 
>>> Why not simply use the 'text' data type?
>>> 
>>> To change the data type on a column you can use:
>>> ALTER [ COLUMN ] /column_name/ [ SET DATA ] TYPE /data_type/ [ COLLATE 
>>> /collation/ ] [ USING /expression/ ]
>>> 
>>> see:
>>> https://www.postgresql.org/docs/9.2/static/sql-altertable.html 
>>> 
>>> 
>>> 
>>> Note that 9.5 is the latest version of pg, with 9.6 being released very 
>>> soon!
>>> 
>>> 
>>> Cheers,
>>> Gavin
>>> 
>> 
>> So I guess the answer to the question is:
>> - Find all occurrences of a_city
>> - Change the type of those columns to text (or varchar(80))
>> - Drop the domain
>> - Recreate with the proper definition. I agree with Gavin that text is a 
>> better choice. Experience has taught me that server side size constraint are 
>> more trouble than they're worth and that size constraints are better handled 
>> on the client side.
>> - Change the type of the columns back to the domain.
>> 
>> 
>> 
>> 
>> 
>> -- 
>> 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] Chante domain type - Postgres 9.2

[Rakesh Kumar]

>You sound like you think that varchar(50) is somehow cheaper than text.

The biggest impediment to text cols in other RDBMS  is no index allowed.
If PG has an elegant solution to that, then yes I see the point made by the
original poster.

Re: [GENERAL] Chante domain type - Postgres 9.2

[Tom Lane]

Rakesh Kumar  writes:
> Can you elaborate?  Why would anyone create a text column to store customer 
> name or product name which can very well be in varchar(50) type of cols.

You sound like you think that varchar(50) is somehow cheaper than text.
That's backwards (at least in PG, other DBMSes may be different).
There's no advantage storage-wise, and there is a cost, namely the cost
of applying the length check on every update.

If you feel that you must have a check for application-specific reasons,
then sure, use varchar(n).  But the number had better be one that you
can trace to crystal-clear application requirements.  varchar(n) where
n has been plucked from the air is a good sign of bad database design.

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] Chante domain type - Postgres 9.2

[Rakesh Kumar]

>I have done some research after converting my database from MySQL 5.6 to 
>PostgreSQL 9.6 (the best move I have ever made),
>and the consensus I found can be summed up as:

>1.  Never, neve, never use VARCHAR or even CHAR
>2. Always always, always use TEXT

>Unless, that is, you have some kind of edge case. This may require a little 
>work upfront, but it will save you from a
>TON of grief down the road.

Can you elaborate?  Why would anyone create a text column to store customer 
name or product name which can very well be in varchar(50) type of cols.

Re: [GENERAL] Chante domain type - Postgres 9.2

[Michael Sheaver]

I have done some research after converting my database from MySQL 5.6 to 
PostgreSQL 9.6 (the best move I have ever made), and the consensus I found can 
be summed up as:
1.  Never, neve, never use VARCHAR or even CHAR
2. Always always, always use TEXT

Unless, that is, you have some kind of edge case. This may require a little 
work upfront, but it will save you from a TON of grief down the road.


> On Sep 26, 2016, at 8:29 AM, Jan de Visser  wrote:
> 
> On 2016-09-26 1:15 AM, Gavin Flower wrote:
> 
>> On 26/09/16 17:58, Patrick B wrote:
>>> Hi guys,
>>> 
>>> I've got this domain:
>>> 
>>>CREATE DOMAIN public.a_city
>>>  AS character varying(80)
>>>  COLLATE pg_catalog."default";
>>> 
>>> 
>>> And I need to increase the type from character varying(80) to character 
>>> varying(255).
>>> 
>>> How can I do that? didn't find info about it. I'm using Postgres 9.2
>>> 
>>> Thanks!
>>> Patrick
>> 
>> Why not simply use the 'text' data type?
>> 
>> To change the data type on a column you can use:
>> ALTER [ COLUMN ] /column_name/ [ SET DATA ] TYPE /data_type/ [ COLLATE 
>> /collation/ ] [ USING /expression/ ]
>> 
>> see:
>> https://www.postgresql.org/docs/9.2/static/sql-altertable.html
>> 
>> 
>> Note that 9.5 is the latest version of pg, with 9.6 being released very soon!
>> 
>> 
>> Cheers,
>> Gavin
>> 
> 
> So I guess the answer to the question is:
> - Find all occurrences of a_city
> - Change the type of those columns to text (or varchar(80))
> - Drop the domain
> - Recreate with the proper definition. I agree with Gavin that text is a 
> better choice. Experience has taught me that server side size constraint are 
> more trouble than they're worth and that size constraints are better handled 
> on the client side.
> - Change the type of the columns back to the domain.
> 
> 
> 
> 
> 
> -- 
> 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] Chante domain type - Postgres 9.2

[Jan de Visser]

On 2016-09-26 1:15 AM, Gavin Flower wrote:


On 26/09/16 17:58, Patrick B wrote:

Hi guys,

I've got this domain:

CREATE DOMAIN public.a_city
  AS character varying(80)
  COLLATE pg_catalog."default";


And I need to increase the type from character varying(80) to 
character varying(255).


How can I do that? didn't find info about it. I'm using Postgres 9.2

Thanks!
Patrick


Why not simply use the 'text' data type?

To change the data type on a column you can use:
ALTER [ COLUMN ] /column_name/ [ SET DATA ] TYPE /data_type/ [ COLLATE 
/collation/ ] [ USING /expression/ ]


see:
https://www.postgresql.org/docs/9.2/static/sql-altertable.html


Note that 9.5 is the latest version of pg, with 9.6 being released 
very soon!



Cheers,
Gavin



So I guess the answer to the question is:
- Find all occurrences of a_city
- Change the type of those columns to text (or varchar(80))
- Drop the domain
- Recreate with the proper definition. I agree with Gavin that text is a 
better choice. Experience has taught me that server side size constraint 
are more trouble than they're worth and that size constraints are better 
handled on the client side.

- Change the type of the columns back to the domain.





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

[GENERAL] Replication slot on master failure

[hariprasath nallasamy]

Hi all
   We are using replication slot for capturing some change sets to
update dependent tables.

   Will there be inconsistency if the master fails and the standby
takes the role of master.?


cheers
-harry

Re: [GENERAL] postgres failed to start from services manager on windows 2008 r2

[Michael Paquier]

On Mon, Sep 26, 2016 at 4:01 PM, PHANIKUMAR G  wrote:
> We have registered postgres(version 9.3.4) as windows service on windows
> 2008 R2 and registration got succeeded.

Just a random thought: do you have ASLR enabled in this build (from
where is this build)? You can check that easily using dumpbin /headers
and by looking for "Dynamic base". Also, you could redirect the logs
to pg_log by modifying manually postgresql.conf before starting it.
This would provide more information regarding why postgres is not
running.
-- 
Michael


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

[GENERAL] postgres failed to start from services manager on windows 2008 r2

[PHANIKUMAR G]

hi,

   We are able to start the postgres from command line without any issue,
if postgres is registered as windows service and tried to start it, we are
facing an issue .

*Problem:*

We have registered postgres(version 9.3.4) as windows service on windows
2008 R2 and registration got succeeded.

we are performing below 2 commands to register postgres as windows service

*pg_ctl.exe register -N "prostgresService" -D
"fullpath_to_data_directory" -W*

*>sc description prostgresService "prostgresService Applicatio*
*n Server Database Service"*

when we try to start the service from services manager(services.msc) it is
failing and popping up below message

*"The prostgres service on Local Computer started and then stopped. Some
services stop automatically if they are not in use by other services or
programs "*

postgres does not even create log and its directory pg_log in pgsql\data
directory.

windows event log has a message

*The description for Event ID 0 from source PostgreSQL cannot be found.
Either the component that raises this event is not installed on your local
computer or the installation is corrupted. You can install or repair the
component on the local computer.*

*If the event originated on another computer, the display information had
to be saved with the event.*

*The following information was included with the event: *

*Timed out waiting for server startup*


could you please help us how to start, we are clueless since postgres is
not creating log except windows event log.


thanks in advance
Phani Kumar