Re: [GENERAL] VM-Ware Backup of VM safe?
> -Ursprüngliche Nachricht- > Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] Im Auftrag von George Neuner > Gesendet: Donnerstag, 21. September 2017 18:35 > > I can't speak for all VM managers, but Vmware's standard static snapshots > *do* capture both the memory and power states of the machine. If a snapshot > is taken while a machine is running, restarting from that snapshot is the same > as if the machine woke up from suspension. Thanks for clarification. > > > >I may be wrong, but my understanding of a VSS writer is that all > >transaction and log files are flushed to disk prior tot he snapshot. > > You understanding is correct - but I think you are maybe misapplying it to this > case. VSS operates in the *host*, not in the virtual machine. And VSS is purely > a Windows mechanism - it does not apply in Unix or Linux. [...] I am aware that VSS is purely Windows, and your comment about VSS aware application is true. Backup programs / VM managers like Data Protection Manager trigger these applications to put the files into a safe state prior to the snapshot. As far as I am aware, MS SQL server provides this mechanism while PostgreSQL does not. And for this reason, I would certainly encourage using PostgreSQL's backup mechanisms for business critical applications. I just spoke with several admins who were not aware of these differences between several databases. Klaus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [SPAM] Re: [GENERAL] VM-Ware Backup of VM safe?
> -Ursprüngliche Nachricht- > Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] Im Auftrag von Moreno Andreo > Gesendet: Mittwoch, 20. September 2017 17:42 > I may be wrong, as I don't know MS SQL Server, but in the way you describe it, > you make me come in mind pg_basebackup(). A VSS writer just prepares a snapshot of the (virtual) machine while pg_basebackup make actually a (safe) copy of the cluster - important differences are that VSS writer 1) is called by the operating system, 2) is much faster and 3) does not need space for a complete backup. But you are right: if you can ensure that pg_basebackup has finished prior to making a backup of the virtual machine, this is certainly safe. Klaus --- Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft. https://www.avast.com/antivirus -- 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] VM-Ware Backup of VM safe?
> Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] Im Auftrag von George Neuner > > But VSS is needed only to copy VM files *while* they are in use. If you > snapshot > the VM, the snapshot files then are read-only and can be freely copied. As > long > as the backup avoids the currently active files, there is no danger. But if you take a snapshot without VSS writer functions, I tend to believe that there is a serious potential that relevant information is still only in RAM and not written to disk. This would get lost in the snapshot. I may be wrong, but my understanding of a VSS writer is that all transaction and log files are flushed to disk prior tot he snapshot. Klaus --- Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft. https://www.avast.com/antivirus -- 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] VM-Ware Backup of VM safe?
> -Ursprüngliche Nachricht- > Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] Im Auftrag von Thomas Güttler > Gesendet: Mittwoch, 20. September 2017 10:03 > An: pgsql-general@postgresql.org > Betreff: [GENERAL] VM-Ware Backup of VM safe? > > We run a PostgreSQL 9.6 server in a virtual machine. > > The virtual machine is managed by the customer. > > He does backup the VM. > > Is this enough, is this safe? When you run MS SQL Server on Hyper-V / Windows, the SQL Server provides ist own VSS writer responding to a taking a snapshot. This ensures that the image oft he SQL database is in a safe stake when the backup is written. I am not sure about VM-Ware, but in any case I don't think that PostgreSQL provides anything similar to a VSS writer (neither on Windows nor on Linux), so the answer is most likely "no, it is not safe". Regards, Klaus --- Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft. https://www.avast.com/antivirus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade --link on Windows
> -Ursprüngliche Nachricht- > > I apologize for not being smarter on this thread. When I helped with the > Windows port, I was told Windows didn't have hard links for use by tablespace > directories, so I got it into my head that Windows didn't have hard links. > Therefore, when I was writing the docs, I called them junction points. It's actually not "Windows" providing hard links, it is the file system NTFS. FAT and its modern cousins don't provide hard links - but this will rarely be used for databases these days. However, ReFS (introduced with server 2012 and providing some new features like automatic integrity checks, clustering etc.) does no longer provide hard links. Are hard links used anywhere else but in pg_upgrade? Klaus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] LIMIT clause slowing down query in some cases, accelerating in others
Running PostgreSQL 9.6 on a Windows Server. Table "t" is kind of a materialized view with > 100 columns and 2.24 Mio rows. Queries are generated by an ORM framework - fairly difficult to modify. Vacuum analyze was carried out - no impact. The framework generates queries like this: select N0."uorderid" from "t" N0 where (N0."szzip" like E'33%') order by N0."szzip" asc nulls last LIMIT 128 OFFSET 0 EXPLAIN ANALYZE: Limit (cost=0.43..547.08 rows=128 width=21) (actual time=402.247..402.386 rows=128 loops=1) -> Index Only Scan using t_szzip_uorderid_idx1 on t n0 (cost=0.43..83880.65 rows=19641 width=21) (actual time=402.244..402.344 rows=128 loops=1) Filter: ((szzip)::text ~~ '33%'::text) Rows Removed by Filter: 699108 Heap Fetches: 0 Planning time: 0.687 ms Execution time: 402.443 ms EXPLAIN ANALYZE without LIMIT and OFFSET: Sort (cost=66503.14..66552.24 rows=19641 width=21) (actual time=151.598..156.155 rows=24189 loops=1) Sort Key: szzip Sort Method: quicksort Memory: 2658kB -> Bitmap Heap Scan on t n0 (cost=200.22..65102.58 rows=19641 width=21) (actual time=21.267..90.272 rows=24189 loops=1) Recheck Cond: ((szzip)::text ~~ '33%'::text) Rows Removed by Index Recheck: 26 Heap Blocks: exact=23224 -> Bitmap Index Scan on t_szzip_idx_gin (cost=0.00..195.31 rows=19641 width=0) (actual time=14.235..14.235 rows=24215 loops=1) Index Cond: ((szzip)::text ~~ '33%'::text) Planning time: 0.669 ms Execution time: 161.860 ms With LIMIT, a btree index is used whereas without the LIMIT clause, a GIN index is used. Unfortunately, modifying the LIKE query parameter from E'33%' to E'10%' gives completely different results: select N0."uorderid" from "t" N0 where (N0."szzip" like E'10%') order by N0."szzip" asc nulls last LIMIT 128 OFFSET 0 EXPLAIN ANALYZE: Limit (cost=0.43..195.08 rows=128 width=21) (actual time=88.699..88.839 rows=128 loops=1) -> Index Only Scan using t_szzip_uorderid_idx1 on t n0 (cost=0.43..83880.65 rows=55158 width=21) (actual time=88.696..88.793 rows=128 loops=1) Filter: ((szzip)::text ~~ '10%'::text) Rows Removed by Filter: 142107 Heap Fetches: 0 Planning time: 0.669 ms Execution time: 88.900 ms EXPLAIN ANALYZE without LIMIT and OFFSET: Index Only Scan using t_szzip_uorderid_idx1 on t n0 (cost=0.43..83880.65 rows=55158 width=21) (actual time=88.483..1263.396 rows=53872 loops=1) Filter: ((szzip)::text ~~ '10%'::text) Rows Removed by Filter: 2192769 Heap Fetches: 0 Planning time: 0.671 ms Execution time: 1274.761 ms In this case, the GIN index is not used at all. Anything else I can do about this?
Re: [GENERAL] Why is this functional index not used?
I played around with COST up to 99 – still no improvement. HOWEVER – I determined that more than 95% of the records in the database are local addresses and the indexed function returns NULL. This seems to create an issue with LEFT JOIN. Increasing the COST of the function to 2 and rewriting the query as follows provides a significantly better result: explain analyse SELECT GCountry.szISOAlpha2, GCountry.szISOAlpha3, GCountry.szISONum3 from t.order torder join G.Country GCountry ON G.GetXmlAddressCountryID(TOrder.szXmlAddress) = GCountry.oID UNION ALL SELECT 'XX'::TEXT, -- can use NULL::TEXT with no difference 'XXX'::TEXT, -- can use NULL::TEXT with no difference '999'::TEXT -- can use NULL::TEXT with no difference from t.order torder WHERE G.GetXmlAddressCountryID(TOrder.szXmlAddress) IS NULL Gives: Append (cost=0.29..7661.82 rows=13578 width=11) (actual time=0.106..20.464 rows=13510 loops=1) -> Nested Loop (cost=0.29..7386.57 rows=13510 width=11) (actual time=0.104..1.235 rows=73 loops=1) -> Seq Scan on country gcountry (cost=0.00..14.58 rows=258 width=27) (actual time=0.012..0.126 rows=258 loops=1) -> Index Scan using order_getxmladdresscountryid_fidx on "order" torder (cost=0.29..27.89 rows=68 width=366) (actual time=0.003..0.003 rows=0 loops=258) Index Cond: (g.getxmladdresscountryid(szxmladdress) = gcountry.oid) -> Index Scan using order_getxmladdresscountryid_fidx on "order" torder_1 (cost=0.29..139.47 rows=68 width=0) (actual time=0.013..12.840 rows=13437 loops=1) Index Cond: (g.getxmladdresscountryid(szxmladdress) IS NULL) Planning time: 1.104 ms Execution time: 23.607 ms A similar result was achieved by return a dummy ID from the function for records without country. Any idea why the functional index does not work in a LEFT JOIN query? Thanks Klaus Von: Jeff Janes [mailto:jeff.ja...@gmail.com] Gesendet: Dienstag, 21. März 2017 08:22 An: Klaus P. Pieper Betreff: Re: [GENERAL] Why is this functional index not used? On Mon, Mar 20, 2017 at 1:25 PM, Klaus P. Pieper < <mailto:kpi6...@gmail.com> kpi6...@gmail.com> wrote: I played around with COST of the function between 1 and 2 and with several options on postgresql.conf without luck. Why not more than 2 ? The true value could be much higher than that. Cheers, Jeff
[GENERAL] Why is this functional index not used?
Given a country table and an order table:
CREATE TABLE g.country
(
-- inherited from table g.standard: oid uuid NOT NULL,
--
some more columns inherited
lisocode integer NOT NULL, -- Numeric ISO 3166 code
szisoalpha2 character varying(2), -- The 2 letter country code
szisoalpha3 character varying(3), -- The 3 letter country code
szisonum3 character varying(3), -- The NUMERIC country code with leading
zeros
-- a few more columns here
CONSTRAINT country_pkey PRIMARY KEY (oid)
) INHERITS (g.standard)
WITH (
OIDS=FALSE
);
This table is filled with ISO 3166 country codes.
The order table:
CREATE TABLE t."order"
(
-- inherited from table g.standard: oid uuid NOT NULL,
--
some more columns inherited
szxmladdress text,
-- many more columns in this table
CONSTRAINT country_pkey PRIMARY KEY (oid)
) INHERITS (g.standard)
WITH (
OIDS=FALSE
);
A typical entry in t."order".szxmladdress looks like
ae0eb84f-9b8b-4fef-b87a-d6757bdfeaf9
0bbdb48c-21c7-429e-944e-59a4d9ace9d5
Hauptstraße
No XML field in the order table exceeds 2kB.
Getting the 2 letter country code from the xml address by this function:
CREATE OR REPLACE FUNCTION g.getxmladdresscountryid(xaddr text)
RETURNS uuid AS
$BODY$BEGIN
RETURN (SELECT oID FROM G.Country WHERE szIsoAlpha2 =
array_to_string(xpath('/address/@country', xAddr::xml), '') ORDER BY
lIsoCode LIMIT 1);
END$BODY$
LANGUAGE plpgsql IMMUTABLE;
I know that this function is not really IMMUTABLE but the country table is
changing only every several years.
Created a functional index on the order table:
CREATE INDEX order_getxmladdresscountryid_fidx
ON t."order"
USING btree
(g.getxmladdresscountryid(szxmladdress));
Joining order and country table limiting to 10 rows uses the index:
explain analyse
SELECT
GCountry.szISOAlpha2,
GCountry.szISOAlpha3,
GCountry.szISONum3
from
t.order torder
left join G.Country GCountry ON
G.GetXmlAddressCountryID(TOrder.szXmlAddress) = GCountry.oID
limit 10
Gives:
Limit (cost=0.56..8.45 rows=10 width=11) (actual time=0.644..4.764 rows=10
loops=1)
-> Merge Right Join (cost=0.56..10670.45 rows=13517 width=11) (actual
time=0.642..4.754 rows=10 loops=1)
Merge Cond: (gcountry.oid =
g.getxmladdresscountryid(torder.szxmladdress))
-> Index Scan using country_pkey on country gcountry
(cost=0.27..38.05 rows=258 width=27) (actual time=0.025..0.067 rows=32
loops=1)
-> Index Scan using order_getxmladdresscountryid_fidx on "order"
torder (cost=0.29..7019.04 rows=13517 width=366) (actual time=0.020..0.058
rows=10 loops=1)
Planning time: 0.603 ms
Execution time: 4.898 ms
But when I remove the limit 10, the index is no longer used:
Hash Left Join (cost=17.81..5397.46 rows=13517 width=11) (actual
time=0.941..4721.372 rows=13510 loops=1)
Hash Cond: (g.getxmladdresscountryid(torder.szxmladdress) = gcountry.oid)
-> Seq Scan on "order" torder (cost=0.00..3504.17 rows=13517 width=366)
(actual time=0.011..27.542 rows=13510 loops=1)
-> Hash (cost=14.58..14.58 rows=258 width=27) (actual time=0.427..0.427
rows=258 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 23kB
-> Seq Scan on country gcountry (cost=0.00..14.58 rows=258
width=27) (actual time=0.008..0.226 rows=258 loops=1)
Planning time: 0.580 ms
Execution time: 4728.602 ms
4.8 seconds - I would expect a much faster query using a functional index.
Even a limit 100 does not use the index any more.
Just a side note: without limit 10 the query needs 4728 ms - almost a
linear increase from 10 rows to 13500 rows.
I played around with COST of the function between 1 and 2 and with
several options on postgresql.conf without luck.
A sequential scan on the order table alone is carried out in 15 ms.
Thanks for any idea.
Klaus
[GENERAL] Toolchain for extensions (language C, Visual Studio 2013 / 2015)
I'm using the setting "Toolchain: Visual Studio 2010" for C language extensions. Is this still correct for the current versions? Thanks Klaus
[GENERAL] ON CONFLICT DO for UPDATE statements
We run two separate databases which are synchronized through our own replication system (two master databases, so we use our own trigger based methods). Let's call the two databases "main" and "remote". >From time to time we get constraint violations on sorting fields classified >UNIQUE when users add records belonging the same data entity: Create table orderpos ( int id not null primary key, int order not null, -- the entity int sort not null, -- sorting criteria for reporting purposes text data, UNIQUE (order, sort) ); I know that with PostgreSQL 9.5 I can use UPSERT to modify the field "sort" when adding it to the remote database. I simply change the content of the field sort to MAX(sort)+1 for this order. This will resolve the issue on the remote side. However, the modified value will be replicated back to the main database where the record was generated. My concern is that in the meantime someone might have added another order position to the master database and the value coming back from the remote database is no longer UNIQUE. Is there a similar concept like UPSERT for UPDATE statements? I.e. UPDATE orderpos SET sort = 2 WHERE if = 4 ON CONFLICT sort DO UPDATE SET sort = GetSafeSortValue(2) If not, what is the best way of avoiding a constraint violation in UPDATE statements? - A BEFORE UPDATE trigger checking the UNIQUE condition and calling GetSafeSort() in case of a conflict? - A rule? - Can triggers be used to catch constraint violations? My least preferred solution would by the replication software handling an exception. Thanks Klaus
[GENERAL] ON CONFLICT DO for UPDATE statements
We run two separate databases which are synchronized through our own replication system (two master databases, so we use our own trigger based methods). Let's call the two databases "main" and "remote". >From time to time we get constraint violations on sorting fields classified UNIQUE when users add records belonging the same data entity: Create table orderpos ( int id not null primary key, int order not null, -- the entity int sort not null, -- sorting criteria for reporting purposes text data, UNIQUE (order, sort) ); I know that with PostgreSQL 9.5 I can use UPSERT to modify the field "sort" when adding it to the remote database. I simply change the content of the field sort to MAX(sort)+1 for this order. This will resolve the issue on the remote side. However, the modified value will be replicated back to the main database where the record was generated. My concern is that in the meantime someone might have added another order position to the master database and the value coming back from the remote database is no longer UNIQUE. Is there a similar concept like UPSERT for UPDATE statements? I.e. UPDATE orderpos SET sort = 2 WHERE if = 4 ON CONFLICT sort DO UPDATE SET sort = GetSafeSortValue(2) If not, what is the best way of avoiding a constraint violation in UPDATE statements? - A BEFORE UPDATE trigger checking the UNIQUE condition and calling GetSafeSort() in case of a conflict? - A rule? - Can triggers be used to catch constraint violations? My least preferred solution would by the replication software handling an exception. Thanks Klaus --- Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft. https://www.avast.com/antivirus
Re: [GENERAL] Allow disabling folding of unquoted identifiers to lowercase
> -Ursprüngliche Nachricht- > > What exactly is the problem you are trying to solve? > > If you and your users are consistent about never using quotes, your users can > write: > > SELECT MyColumn FROM MyTable ORDER BY MyColumn; > > It will select mycolumn from mytable, but that doesn't matter, since you > created the table with > > CREATE MyTable (MyColumn varchar); > > so you really have a table mytable with a column mycolumn, not a table > MyTable with a column MyColumn. I use 50% of my time Sybase and 50% PostgreSQL. For me is the way Sybase works is just more convenient: CREATE MyTable (MyColumn varchar); creates a camel cased table MyType and field MyColumn. SELECT * FROM SYSCATALOG gives MyTable. This is better readable when you use long table / fiel names. The user can then use whatever he wants: mytable, MyTable, myTABLE, ... Klaus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Experience with PL/xx?
Hi, does anybody out there have experience with the several PL's which are available for PostgreSQL? I am currently evaluating several databases (commercial as well as free & open source) for a new project and would just like to hear some feedback. PL/Java seems to be developed by a fairly small team - no updates on their website since December 2002 (and even what's available on this web site is not very encouraging to use PL/Java on a production system). Does anybody use PL/Java? What about PL/Python or PL/Ruby? Any experience with these two implementations on a production system? Are there any PostgreSQL specifics or limitations? Significant differences between these two languages? Any input will be apprecíated, Klaus ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
