Re: (multiplatform) replication problem

[Jeff Janes]

On Tue, Jan 8, 2019 at 10:49 AM W.P.  wrote:

> Hi there,
> I have following setup:
> - master database, 9.5.7, on I386 (Pentium M),
> now i want to replicate database to:
> - slave database. 9.5.7 on armhf (OrangePiPC+).
> Is in possible?
>

I think the error message is telling you that physical replication is not
possible across platforms as different as this.  You could go through the
output of pg_controldata line by line to see what the differences are.
Although perhaps someone can just tell you off the top of their head.

Perhaps you can use logical replication instead, but to do that you should
probably upgrade your PostgreSQL version to at least v10.

Cheers,

Jeff

Re: Pulling data from Postgres DB table for every 5 seconds.

[github kran]

>
>
> Hi Postgres Team,
>
> I have an application using RDS Aurora Postgresql 9.6 version having 4 TB
> of DB size. In this DB we have a table PRODUCT_INFO with around  1 million
> rows and table size of 1 GB.
> We are looking for a implementation where we want to pull the data in real
> time for every 5 seconds from the DB ( Table mentioned above) and send it
> to IOT topic whenever an event occurs for a product. ( event is any new
> product information or change in the existing
> product information.).
>
> This table has few DML operations in real time either INSERT or UPDATE
> based on the productId. ( Update whenever there is a change in the product
> information and INSERT when a record doesnt exists for that product).
>
> We have REST API's built in the backend pulling data from this backend RDS
> Aurora POSTGRES DB and used by clients.
>
> *UseCase*
> We dont want clients to pull the data for every 5 seconds from DB but
> rather provide a service which can fetch the data from DB in real time and
> push the data to IOT topic by pulling data for every 5 seconds from DB.
>
> *Questions*
> 1) How can I get information by pulling from the DB every 5 seconds
> without impacting the performance of the DB.
> 2) What are the options I have pulling the data from this table every 5
> seconds. Does POSTGRES has any other options apart from TRIGGER ?.
>
>
> Any ideas would be helpful.
>
> Thanks !!
> GithubKran
>

Re: Pulling data from Postgres DB table for every 5 seconds.

[Rob Sargent]

> On Jan 9, 2019, at 10:02 AM, github kran  wrote:
> 
> 
> Hi Postgres Team,
> 
> I have an application using RDS Aurora Postgresql 9.6 version having 4 TB of 
> DB size. In this DB we have a table PRODUCT_INFO with around  1 million rows 
> and table size of 1 GB.
> We are looking for a implementation where we want to pull the data in real 
> time for every 5 seconds from the DB ( Table mentioned above) and send it to 
> IOT topic whenever an event occurs for a product. ( event is any new product 
> information or change in the existing 
> product information.). 
> 
> This table has few DML operations in real time either INSERT or UPDATE based 
> on the productId. ( Update whenever there is a change in the product 
> information and INSERT when a record doesnt exists for that product).
> 
> We have REST API's built in the backend pulling data from this backend RDS 
> Aurora POSTGRES DB and used by clients. 
> 
> UseCase
> We dont want clients to pull the data for every 5 seconds from DB but rather 
> provide a service which can fetch the data from DB in real time and push the 
> data to IOT topic by pulling data for every 5 seconds from DB.
> 
> Questions
> 1) How can I get information by pulling from the DB every 5 seconds without 
> impacting the performance of the DB.
> 2) What are the options I have pulling the data from this table every 5 
> seconds. Does POSTGRES has any other options apart from TRIGGER ?.
> 
> 
> Any ideas would be helpful.
> 
> Thanks !!
> GithubKran

There is DML event trapping.  You don’t poll every 5seconds you react 
immediately to each event (with trigger or event).  From the trigger 
perspective you probably have everything you need to update IOT with addition 
searching.

Re: Pulling data from Postgres DB table for every 5 seconds.

[Ron]

On 1/9/19 11:02 AM, github kran wrote:



Hi Postgres Team,

I have an application using RDS Aurora Postgresql 9.6 version having 4
TB of DB size. In this DB we have a table PRODUCT_INFO with around  1
million rows and table size of 1 GB.
We are looking for a implementation where we want to pull the data in
real time for every 5 seconds from the DB



"the data".  All 1GB every 5 seconds?


( Table mentioned above) and send it to IOT topic whenever an event
occurs for a product. ( event is any new product information or change
in the existing
product information.).

This table has few DML operations in real time either INSERT or UPDATE
based on the productId. ( Update whenever there is a change in the
product information and INSERT when a record doesnt exists for that
product).

We have REST API's built in the backend pulling data from this backend
RDS Aurora POSTGRES DB and used by clients.

*_UseCase_*
We dont want clients to pull the data for every 5 seconds from DB but
rather provide a service which can fetch the data from DB in real time
and push the data to IOT topic by pulling data for every 5 seconds
from DB.



Or just a tiny subset every 5 seconds?



*_Questions_*
1) How can I get information by pulling from the DB every 5 seconds
without impacting the performance of the DB.
2) What are the options I have pulling the data from this table every
5 seconds. Does POSTGRES has any other options apart from TRIGGER ?.


Any ideas would be helpful.

Thanks !!
GithubKran



--
Angular momentum makes the world go 'round.

Re: Pulling data from Postgres DB table for every 5 seconds.

[github kran]

Thanks for your reply Rob. Reading the below documentation link says the
EVENT trigger is only supported for DDL commands. Is it not correct ?.

*1) https://www.postgresql.org/docs/9.6/event-trigger-definition.html
 *
(An event trigger fires whenever the event with which it is associated
occurs in the database in which it is defined. Currently, the only
supported events are ddl_command_start, ddl_command_end, table_rewrite and
sql_drop. Support for additional events may be added in future releases.).
2) Doesnt the trigger slow down inserts/update we are doing to the table ?.
Does it slow down if we are reading the data using the API when we have a
trigger in place ?.

Ron- Its a tiny subset of 1 GB Data for every 5 seconds but not on the
entire data.


Thanks !!.

On Wed, Jan 9, 2019 at 11:10 AM Rob Sargent  wrote:

>
>
> On Jan 9, 2019, at 10:02 AM, github kran  wrote:
>
>
>> Hi Postgres Team,
>>
>> I have an application using RDS Aurora Postgresql 9.6 version having 4 TB
>> of DB size. In this DB we have a table PRODUCT_INFO with around  1 million
>> rows and table size of 1 GB.
>> We are looking for a implementation where we want to pull the data in
>> real time for every 5 seconds from the DB ( Table mentioned above) and send
>> it to IOT topic whenever an event occurs for a product. ( event is any new
>> product information or change in the existing
>> product information.).
>>
>> This table has few DML operations in real time either INSERT or UPDATE
>> based on the productId. ( Update whenever there is a change in the product
>> information and INSERT when a record doesnt exists for that product).
>>
>> We have REST API's built in the backend pulling data from this backend
>> RDS Aurora POSTGRES DB and used by clients.
>>
>> *UseCase*
>> We dont want clients to pull the data for every 5 seconds from DB but
>> rather provide a service which can fetch the data from DB in real time and
>> push the data to IOT topic by pulling data for every 5 seconds from DB.
>>
>> *Questions*
>> 1) How can I get information by pulling from the DB every 5 seconds
>> without impacting the performance of the DB.
>> 2) What are the options I have pulling the data from this table every 5
>> seconds. Does POSTGRES has any other options apart from TRIGGER ?.
>>
>>
>> Any ideas would be helpful.
>>
>> Thanks !!
>> GithubKran
>>
>
> There is DML event trapping.  You don’t poll every 5seconds you react
> immediately to each event (with trigger or event).  From the trigger
> perspective you probably have everything you need to update IOT with
> addition searching.
>

Re: multiple configurations with repmgr

[Martín Marqués]

El 8/1/19 a las 13:17, ROS Didier escribió:
> Hi
> 
>    We are going to use repmgr  with one node for the
> primary, one node for the standby and one node for the witness.
> 
>    It works fine _with one project_.
> 
>  
> 
> The problem is  that we want to have several other projects. Each one
> with its own primary node, its own standby node and the _same witness node._
> 
>    Question : is it possible to do that ? _One witness node_
> which surveys several other nodes.

Yes, you can accomplish that following the instructions below for
configuring the witness:

- Initialize the postgres server on the witness node
- Create one database for each cluster the witness will be managing
- Create the appropriate repmgr.conf files, one for each cluster managed
through this witness, and each with the appropriate conninfo setting so
they point to different databases.
- Run `repmgr witness register` multiple times, using different
configuration files on each run

Hope that helps clarify.

P.S.: This works on latest versions of repmgr. Before 3.3 the witness
was created through repmgr which made things harder to manage, reason
why that was removed in later versions

Cheers,

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Re: Pulling data from Postgres DB table for every 5 seconds.

[Mark Fletcher]

On Wed, Jan 9, 2019 at 9:02 AM github kran  wrote:

>
>> Hi Postgres Team,
>>
>> I have an application using RDS Aurora Postgresql 9.6 version having 4 TB
>> of DB size. In this DB we have a table PRODUCT_INFO with around  1 million
>> rows and table size of 1 GB.
>> We are looking for a implementation where we want to pull the data in
>> real time for every 5 seconds from the DB ( Table mentioned above) and send
>> it to IOT topic whenever an event occurs for a product. ( event is any new
>> product information or change in the existing
>> product information.).
>>
>>
It's unclear whether you want to do table scans or if you're just looking
for changes to the database. If you're looking just for changes, consider
implementing something using logical replication. We have a logical
replication system set up to stream changes from the database into an
elastic search cluster, and it works great.

Mark

postgres operational

[Steve Clark]

Hi List,

Is there a sure fire way to tell if postgres server is up an operational. I was 
testing to see if the
socket at /tmp/.s.PGSQL.5432 existed - but I ran into a recent problem on 
CentOS 7.5, postgresql 9.2.24, where the
socket was there but my script couldn't read from my database yet.

Thanks,
Steve
-- 

Re: Pulling data from Postgres DB table for every 5 seconds.

[Rob Sargent]

On 1/9/19 10:21 AM, github kran wrote:
Thanks for your reply Rob. Reading the below documentation link says 
the EVENT trigger is only supported for DDL commands. Is it not correct ?.


_1) https://www.postgresql.org/docs/9.6/event-trigger-definition.html _
(An event trigger fires whenever the event with which it is associated 
occurs in the database in which it is defined. Currently, the only 
supported events are ddl_command_start, ddl_command_end, 
table_rewrite and sql_drop. Support for additional events may be added 
in future releases.).
2) Doesnt the trigger slow down inserts/update we are doing to the 
table ?. Does it slow down if we are reading the data using the API 
when we have a trigger in place ?.



Ah, right you are.  Are triggers off the table?  You would want to write 
the trigger function in some (trusted?) language with access to the outside

RE: postgres operational

[Scot Kreienkamp]

The best way I came up with for older versions is:If timeout -s 9 10 psql 
-d DBNAME -c "select 1" >/dev/null ; then 

And on newer versions, use the pg_isready command.


Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 |  Office: 734-384-6403 |  |  
Mobile: 7349151444 | Email: scot.kreienk...@la-z-boy.com
> -Original Message-
> From: Steve Clark [mailto:steve.cl...@netwolves.com]
> Sent: Wednesday, January 9, 2019 12:59 PM
> To: pgsql 
> Subject: postgres operational
>
> Hi List,
>
> Is there a sure fire way to tell if postgres server is up an operational. I 
> was
> testing to see if the
> socket at /tmp/.s.PGSQL.5432 existed - but I ran into a recent problem on
> CentOS 7.5, postgresql 9.2.24, where the
> socket was there but my script couldn't read from my database yet.
>
> Thanks,
> Steve
> --
>


This message is intended only for the individual or entity to which it is 
addressed.  It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws.  If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information.  If you 
have received this communication in error, please notify us immediately by 
e-mail or by telephone at the above number. Thank you.

Re: Pulling data from Postgres DB table for every 5 seconds.

[github kran]

Mark - We are currently on 9.6 version of postgres and cant use this
feature of logical replication.Answering to your question we are looking
for any changes in the data related to a specific table ( changes like any
update on a timestamp field
OR any new inserts happened in the last 5 seconds for a specific product
entity).
Any other alternatives ?.

On Wed, Jan 9, 2019 at 11:24 AM Mark Fletcher  wrote:

> On Wed, Jan 9, 2019 at 9:02 AM github kran  wrote:
>
>>
>>> Hi Postgres Team,
>>>
>>> I have an application using RDS Aurora Postgresql 9.6 version having 4
>>> TB of DB size. In this DB we have a table PRODUCT_INFO with around  1
>>> million rows and table size of 1 GB.
>>> We are looking for a implementation where we want to pull the data in
>>> real time for every 5 seconds from the DB ( Table mentioned above) and send
>>> it to IOT topic whenever an event occurs for a product. ( event is any new
>>> product information or change in the existing
>>> product information.).
>>>
>>>
> It's unclear whether you want to do table scans or if you're just looking
> for changes to the database. If you're looking just for changes, consider
> implementing something using logical replication. We have a logical
> replication system set up to stream changes from the database into an
> elastic search cluster, and it works great.
>
> Mark
>

Re: Pulling data from Postgres DB table for every 5 seconds.

[github kran]

Rob -   It's a Java based application. We dont have triggers yet on the
table and is trigger a only option in 9.6 version ?.

On Wed, Jan 9, 2019 at 12:01 PM Rob Sargent  wrote:

>
> On 1/9/19 10:21 AM, github kran wrote:
>
> Thanks for your reply Rob. Reading the below documentation link says the
> EVENT trigger is only supported for DDL commands. Is it not correct ?.
>
> *1) https://www.postgresql.org/docs/9.6/event-trigger-definition.html
>  *
> (An event trigger fires whenever the event with which it is associated
> occurs in the database in which it is defined. Currently, the only
> supported events are ddl_command_start, ddl_command_end, table_rewrite
>  and sql_drop. Support for additional events may be added in future
> releases.).
> 2) Doesnt the trigger slow down inserts/update we are doing to the table
> ?. Does it slow down if we are reading the data using the API when we have
> a trigger in place ?.
>
>
> Ah, right you are.  Are triggers off the table?  You would want to write
> the trigger function in some (trusted?) language with access to the outside
>

Re: postgres operational

[Martín Marqués]

El 9/1/19 a las 14:58, Steve Clark escribió:
> Hi List,
> 
> Is there a sure fire way to tell if postgres server is up an operational. I 
> was testing to see if the
> socket at /tmp/.s.PGSQL.5432 existed - but I ran into a recent problem on 
> CentOS 7.5, postgresql 9.2.24, where the
> socket was there but my script couldn't read from my database yet.

Use `check_postgres`


-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Re: Pulling data from Postgres DB table for every 5 seconds.

[Rob Sargent]

> On Jan 9, 2019, at 11:11 AM, github kran  wrote:
> 
> Rob -   It's a Java based application. We dont have triggers yet on the table 
> and is trigger a only option in 9.6 version ?.  
> 
> On Wed, Jan 9, 2019 at 12:01 PM Rob Sargent  > wrote:
> 
> 
> On 1/9/19 10:21 AM, github kran wrote:
>> Thanks for your reply Rob. Reading the below documentation link says the 
>> EVENT trigger is only supported for DDL commands. Is it not correct ?.
>> 
>> 1) https://www.postgresql.org/docs/9.6/event-trigger-definition.html 
>>  
>> (An event trigger fires whenever the event with which it is associated 
>> occurs in the database in which it is defined. Currently, the only supported 
>> events are ddl_command_start, ddl_command_end, table_rewrite and sql_drop. 
>> Support for additional events may be added in future releases.).
>> 2) Doesnt the trigger slow down inserts/update we are doing to the table ?. 
>> Does it slow down if we are reading the data using the API when we have a 
>> trigger in place ?.
>> 
>> 
> Ah, right you are.  Are triggers off the table?  You would want to write the 
> trigger function in some (trusted?) language with access to the outside

(Custom here is to “bottom post”)

Have you tried triggers and found them to have too much impact on total system? 
 I can’t see them being more expensive than looking for changes every 5 
seconds.  If your hardware can scan 1T that quickly then I suspect your trigger 
will not be noticed.  I would have the trigger write to queue and have 
something else using the queue to talk to IOT piece.

Failing that, perhaps your java (server-side?) app making the changes can be 
taught to emit the necessary details to IOT-thingy?

Re: Pulling data from Postgres DB table for every 5 seconds.

[Mark Fletcher]

On Wed, Jan 9, 2019 at 10:10 AM github kran  wrote:

> Mark - We are currently on 9.6 version of postgres and cant use this
> feature of logical replication.Answering to your question we are looking
> for any changes in the data related to a specific table ( changes like any
> update on a timestamp field
> OR any new inserts happened in the last 5 seconds for a specific product
> entity).
> Any other alternatives ?.
>
> The feature was added in 9.4 (I think). We are on 9.6 and it works great.
Not sure about RDS Aurora, however.

Mark

Re: postgres operational

[Ron]

On 1/9/19 12:19 PM, Martín Marqués wrote:

El 9/1/19 a las 14:58, Steve Clark escribió:

Hi List,

Is there a sure fire way to tell if postgres server is up an operational. I was 
testing to see if the
socket at /tmp/.s.PGSQL.5432 existed - but I ran into a recent problem on 
CentOS 7.5, postgresql 9.2.24, where the
socket was there but my script couldn't read from my database yet.

Use `check_postgres`


Isn't "pg_ctl status" the canonical method?


--
Angular momentum makes the world go 'round.

Re: Pulling data from Postgres DB table for every 5 seconds.

[github kran]

On Wed, Jan 9, 2019 at 12:26 PM Rob Sargent  wrote:

>
>
> On Jan 9, 2019, at 11:11 AM, github kran  wrote:
>
> Rob -   It's a Java based application. We dont have triggers yet on the
> table and is trigger a only option in 9.6 version ?.
>
> On Wed, Jan 9, 2019 at 12:01 PM Rob Sargent  wrote:
>
>>
>> On 1/9/19 10:21 AM, github kran wrote:
>>
>> Thanks for your reply Rob. Reading the below documentation link says the
>> EVENT trigger is only supported for DDL commands. Is it not correct ?.
>>
>> *1) https://www.postgresql.org/docs/9.6/event-trigger-definition.html
>>  *
>> (An event trigger fires whenever the event with which it is associated
>> occurs in the database in which it is defined. Currently, the only
>> supported events are ddl_command_start, ddl_command_end, table_rewrite
>>  and sql_drop. Support for additional events may be added in future
>> releases.).
>> 2) Doesnt the trigger slow down inserts/update we are doing to the table
>> ?. Does it slow down if we are reading the data using the API when we have
>> a trigger in place ?.
>>
>>
>> Ah, right you are.  Are triggers off the table?  You would want to write
>> the trigger function in some (trusted?) language with access to the outside
>>
>
> (Custom here is to “bottom post”)
>
> Have you tried triggers and found them to have too much impact on total
> system?  I can’t see them being more expensive than looking for changes
> every 5 seconds.  If your hardware can scan 1T that quickly then I suspect
> your trigger will not be noticed.  I would have the trigger write to queue
> and have something else using the queue to talk to IOT piece.
>
> Failing that, perhaps your java (server-side?) app making the changes can
> be taught to emit the necessary details to IOT-thingy?
>

*Sure will try with a trigger and send data to a Queue and gather data for
every 5 seconds reading off from the queue and send to IOT topic. Already
we have a queue where every message inserted or updated on the table is
sent to a queue but **that is lot of data we are gathering. We want to
rather minimize collecting data from DB.*

Re: Pulling data from Postgres DB table for every 5 seconds.

[github kran]

On Wed, Jan 9, 2019 at 12:36 PM Mark Fletcher  wrote:

> On Wed, Jan 9, 2019 at 10:10 AM github kran  wrote:
>
>> Mark - We are currently on 9.6 version of postgres and cant use this
>> feature of logical replication.Answering to your question we are looking
>> for any changes in the data related to a specific table ( changes like any
>> update on a timestamp field
>> OR any new inserts happened in the last 5 seconds for a specific product
>> entity).
>> Any other alternatives ?.
>>
>> The feature was added in 9.4 (I think). We are on 9.6 and it works great.
> Not sure about RDS Aurora, however.
>
> Mark
>

Mark - just curious to know on the logical replication. Do you think I can
use it for my use case where i need to publish data to a subscriber when
there is a change in the data updated for a row or any new inserts
happening on the table. Intention
is to send this data in Json format by collecting this modified data in
real time to a subscriber.

Tahanks
Kran

Re: Pulling data from Postgres DB table for every 5 seconds.

[Mark Fletcher]

On Wed, Jan 9, 2019 at 12:58 PM github kran  wrote:

>
> Mark - just curious to know on the logical replication. Do you think I can
> use it for my use case where i need to publish data to a subscriber when
> there is a change in the data updated for a row or any new inserts
> happening on the table. Intention
> is to send this data in Json format by collecting this modified data in
> real time to a subscriber.
>
> From what you've said, it's a great use case for that feature. The one
thing to note is that you will have to code up a logical replication
client. If I can do it, pretty much anyone can, but it might take some time
to get things right. I wrote about some of what I found when developing our
client a year ago here:
https://wingedpig.com/2017/09/20/streaming-postgres-changes/

We ended up just using the included test output plugin that comes with the
postgresql distribution. And we didn't end up streaming to Kafka or
anything else first. We just take the data and insert it into our
elasticsearch cluster directly as we get it.

Mark

Re: Pulling data from Postgres DB table for every 5 seconds.

[Martín Marqués]

El 9/1/19 a las 20:22, Mark Fletcher escribió:
> On Wed, Jan 9, 2019 at 12:58 PM github kran  > wrote:
> 
> 
> Mark - just curious to know on the logical replication. Do you think
> I can use it for my use case where i need to publish data to a
> subscriber when there is a change in the data updated for a row or
> any new inserts happening on the table. Intention
> is to send this data in Json format by collecting this modified data
> in real time to a subscriber.
> 
> From what you've said, it's a great use case for that feature. The one
> thing to note is that you will have to code up a logical replication
> client. If I can do it, pretty much anyone can, but it might take some
> time to get things right. I wrote about some of what I found when
> developing our client a year ago
> here: https://wingedpig.com/2017/09/20/streaming-postgres-changes/
> 
> We ended up just using the included test output plugin that comes with
> the postgresql distribution. And we didn't end up streaming to Kafka or
> anything else first. We just take the data and insert it into our
> elasticsearch cluster directly as we get it.

I realy doubt that would work. Aurora doesn't have WALs, so how would
you be able to decode the transactions?

AFAIU, you can't use logical decoding on Aurora. Maybe you should be
asking at the Aurora support channel.

Regards,

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Re: postgres operational

[Martín Marqués]

El 9/1/19 a las 17:38, Ron escribió:
> On 1/9/19 12:19 PM, Martín Marqués wrote:
>> El 9/1/19 a las 14:58, Steve Clark escribió:
>>> Hi List,
>>>
>>> Is there a sure fire way to tell if postgres server is up an
>>> operational. I was testing to see if the
>>> socket at /tmp/.s.PGSQL.5432 existed - but I ran into a recent
>>> problem on CentOS 7.5, postgresql 9.2.24, where the
>>> socket was there but my script couldn't read from my database yet.
>> Use `check_postgres`
> 
> Isn't "pg_ctl status" the canonical method?

Depends on what you are expecting.

`check_postgres` has many different checks, one which will actually try
to connect and run `SELECT 1` to make sure postgres can actually run a
query.

pg_ctl status might return ok, yet you can't connect for other reasons.


-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

CREATE OR REPLACE MATERIALIZED VIEW

[Aidan Samuel]

I've been looking over the syntax for creating various database objects:
tables, views, functions, etc., and I wondered why there seem to be some
discrepancies.

For example, table creation syntax features [ IF NOT EXISTS ] but doesn't
feature [ OR REPLACE ], function creation syntax is the inverse and has [
OR REPLACE ], but no [ IF NOT EXISTS ].

I also noticed that the syntax for creating VIEWS and creating MATERIALISED
VIEWS is handled separately [1][2].

I was expecting to see one page of documentation with the syntax listed as
something like: CREATE [ OR REPLACE ] [ MATERIALIZED ] VIEW [ IF NOT EXISTS
] view_name.

Is this because a materialized view is actually a table and thus inherits
the table creation syntax instead of the view creation syntax?

How come table creation doesn't allow [ OR REPLACE ], and view creation
doesn't allow [ IF NOT EXISTS ]? Is this just how the SQL spec defines
things?

Thanks,
Aidan.


[1] https://www.postgresql.org/docs/current/sql-createview.html
[2] https://www.postgresql.org/docs/current/sql-creatematerializedview.html

Re: CREATE OR REPLACE MATERIALIZED VIEW

[Tom Lane]

Aidan Samuel  writes:
> How come table creation doesn't allow [ OR REPLACE ], and view creation
> doesn't allow [ IF NOT EXISTS ]? Is this just how the SQL spec defines
> things?

I think all that stuff is our own fault rather than something you can
find in the SQL spec.

For indivisible database objects such as functions, IMV the "or replace"
semantics (COR) is better than the "if not exists" semantics (CINE).
With COR, if the command succeeds then you know what properties the
object has: the ones you just gave.  With CINE, you don't really know
anything at all except that there's an object by that name.  So we've
generally preferred to implement COR if practical.

For tables, though, the tradeoffs seem pretty different.  In particular,
COR semantics would more or less imply throwing away the table contents,
and probably dropping existing foreign-key connections too, since none of
that could be inferred from the given command.  It's a little scary to
do that implicitly.  So we insist that if you really want to lose data,
you say DROP TABLE explicitly, and then you can create a fresh empty
table.

(Personally I think CINE is seldom anything but a foot-gun, and would
rather we didn't have it at all; but some folks insisted.)

> I also noticed that the syntax for creating VIEWS and creating MATERIALISED
> VIEWS is handled separately [1][2].

Again, those aren't really the same thing: a matview is a lot more like a
table than it is like a view, so we don't treat it the same.  I think
if you compare the text for the CREATE VIEW and CREATE MATVIEW man pages,
you'll agree that a merged version would be pretty confusing.  All the
options are different.

Admittedly, this is all judgment calls and experience, but that's
why it's like that.

regards, tom lane

Re: CREATE OR REPLACE MATERIALIZED VIEW

[Aidan Samuel]

On Thu, 10 Jan 2019 at 12:13, Tom Lane  wrote:
> For indivisible database objects such as functions, IMV the "or replace"
> semantics (COR) is better than the "if not exists" semantics (CINE)...
>
> For tables, though, the tradeoffs seem pretty different...

Thanks Tom, your explanation makes a lot of sense.