Re: CREATE UNLOGGED MATERIALIZED VIEW

[Mitar]

Hi!

FYI, I started working on also adding TEMPORARY materialized views. [1]

[1] 
https://www.postgresql.org/message-id/CAKLmikOwqJx7J%3Dfg7VFcuk2OUjqLy%3DHZj_J5stY1x8X7%2B14g5g%40mail.gmail.com

UNLOGGED is still unsupported, but TEMPORARY materialized views do not
have the issue mentioned in the comment above because they do not exit
after the crash anyway.


Mitar

On Wed, Dec 26, 2018 at 8:35 AM Tom Lane  wrote:
>
> Mitar  writes:
> > I am looking at parser grammar rules and it looks like the following
> > is a valid query:
> > CREATE UNLOGGED MATERIALIZED VIEW
> > Any particular reason this is not documented?
>
> Because it's not supported:
>
> regression=# CREATE UNLOGGED MATERIALIZED VIEW foo AS select 1;
> ERROR:  materialized views cannot be UNLOGGED
>
> Tracing down the source of that error message, we find
>
> /*
>  * For now, we disallow unlogged materialized views, because it seems
>  * like a bad idea for them to just go to empty after a crash. (If we
>  * could mark them as unpopulated, that would be better, but that
>  * requires catalog changes which crash recovery can't presently
>  * handle.)
>  */
> if (stmt->into->rel->relpersistence == RELPERSISTENCE_UNLOGGED)
> ereport(ERROR,
> (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
>  errmsg("materialized views cannot be UNLOGGED")));
>
> So that's a value judgment you might or might not agree with (and even if
> you don't, it's not clear whether there are any deeper implementation
> problems that would be exposed if we just deleted this prohibition).
> But there it is, and it's why the documentation doesn't mention such a
> capability.
>
> The reason the grammar allows the possibility is likely some combination
> of (1) sharing grammar with other statements, (2) an error like the above
> is a lot more user-friendly than "syntax error" if someone tries to use
> the nonexistent feature, and (3) making provision for future enhancements.
>
> regards, tom lane



-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Re: Watching for view changes

[Mitar]

Hi!

I started a patch to address this limitation. [1]

[1] 
https://www.postgresql.org/message-id/CAKLmikMN%2B0W79A7Wg4krK55cTLxb%3DTARdz2b%3Dvqp19ao6AAZjw%40mail.gmail.com


Mitar

On Mon, Dec 24, 2018 at 1:38 AM Mitar  wrote:
>
> Hi!
>
> On Mon, Dec 24, 2018 at 1:31 AM George Neuner  wrote:
> > Did you execute the view or simply define it?  I think the table is
> > created the 1st time the view is executed.
>
> View is populated with data and I can select from it. Still, I cannot
> find any table which would look like it in pg_tables?
>
>
> Mitar
>
> --
> http://mitar.tnode.com/
> https://twitter.com/mitar_m



-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Ipv6 address stored differently while using Inet type

[Sameer Thakur]

Hello,
I am using Postgres 10.4
I tried to insert ipv6 address '2001:db8:::::1.2.3.4'
in Postgres (10.4) in an inet column. It is stored as
2001:db8:::::102:304.
Why is the IP stored differently?
Does the stored IP represent the same one being inserted?
 Is this a known bug resolved in later versions of Postgres?
Thank you
Sameer

Re: Ipv6 address stored differently while using Inet type

[Peter J. Holzer]

On 2018-12-27 14:56:25 +0530, Sameer Thakur wrote:
> I am using Postgres 10.4
> I tried to insert ipv6 address '2001:db8:::::1.2.3.4'
> in Postgres (10.4) in an inet column. It is stored as
> 2001:db8:::::102:304.

No. It is *displayed* as "2001:db8:::::102:304". It is
stored as a 128 bit sequence. 

> Why is the IP stored differently?

It is displayed in the way IPv6 addresses are usually displayed (16 bit
groups in hex separated by colons). The form you used to insert the
address (first 96 bits in hex and last 32 bits in decimal) is normally
only used for the IPv4 compatibility range: You would write
::::::C000:022A as ::192.0.2.42, not ::C000:22A,
but you would write 2001:db8:::::102:304, not
2001:db8:::::1.2.3.4.

> Does the stored IP represent the same one being inserted?

Yes.

>  Is this a known bug resolved in later versions of Postgres?

No.

hp

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


signature.asc
Description: PGP signature

Question about unlogged to logged conversion

[Bhavin Gandhi]

Hello,
I'm trying to understand for a given unlogged table of a specific size and
# of rows, if I do "alter table" on it to convert it to logged table, is
there a performance difference between 9.5, 9.6 and 10? in other words are
there specific improvements in this area that may not be available in all
Postgres versions?

Also, at a high level, what happens when the table is converted from
Unlogged to logged? Is the operation time dependent on size and number of
rows?

Thanks.

Re: Question about unlogged to logged conversion

[Ravi Krishna]

On Thu, Dec 27, 2018, at 5:23 PM, Bhavin Gandhi wrote:
> Hello,
> I'm trying to understand for a given unlogged table of a specific size
> and # of rows, if I do "alter table" on it to convert it to logged
> table, is there a performance difference between 9.5, 9.6 and 10? in
> other words are there specific improvements in this area that may not
> be available in all Postgres versions?> 

Unlogged tables are not stored in the catalog like a regular table. When
it is converted to a logged table , the entire operation is logged (goes
into wal logs) in one single transaction, blocking the entire table
during the process. IMO this makes unlogged -> logged workflow not a
right fit for large tables.  In other products I have used, large tables
are set to unlogged mode before bulk load and then set back to normal
logged mode.

Re: Question about unlogged to logged conversion

[Michael Paquier]

On Thu, Dec 27, 2018 at 05:52:14PM -0500, Ravi Krishna wrote:
> Unlogged tables are not stored in the catalog like a regular table. When
> it is converted to a logged table , the entire operation is logged (goes
> into wal logs) in one single transaction, blocking the entire table
> during the process. IMO this makes unlogged -> logged workflow not a
> right fit for large tables.  In other products I have used, large tables
> are set to unlogged mode before bulk load and then set back to normal
> logged mode.

If you are interested of how things happen at code level, you can grep
for INIT_FORKNUM, which is the initial state of an unlogged table used
when doing crash recovery.  And while the main fork's data is never
WAL-logged, this initial fork needs to be included in WAL.
--
Michael


signature.asc
Description: PGP signature