Re: Real application clustering in postgres.
## Andrew Kerber (andrew.ker...@gmail.com): > The nice point of oracle > dataguard is that it is a block by block copy, while all of the Postgres > Multi-Master and master-slave replication solutions work by SQL capture. https://www.postgresql.org/docs/12/warm-standby.html#STREAMING-REPLICATION I wouldn't exactly call our streaming replication "SQL capture". Regards, Christoph -- Spare Space
Re: Table with many NULLS for indexed column yields strange query plan
"Peter J. Holzer" writes: > How is the selectivity of "type"? Would an index on that column help? The EXPLAIN results say that the "type = 'Standard'" condition is completely not selective: in both plans, there is no "Rows Removed by Filter" indication where it's applied, indicating that it did not filter out any rows. Which is odd, because if that isn't removing any rows, why is the planner overestimating the number of rows retrieved from table2 by circa 10x? Maybe a lack of stats for the "type" column? I also find it odd that the second plan is uselessly using an index for table3 (I say "useless" because there's no index condition and no apparent need for the result to be sorted). I suspect the OP has been putting his thumb on the scales in ways he hasn't bothered to tell us about, like fooling with the cost parameters and/or disabling seqscans. regards, tom lane
Re: Another INSTEAD OF TRIGGER question
On Sun, Mar 08, 2020 at 10:07:24PM +0100, S??ndor Daku wrote:
> On Sun, 8 Mar 2020 at 21:26, stan wrote:
>
> > On Sun, Mar 08, 2020 at 03:56:56PM +0100, S??ndor Daku wrote:
> > > On Sun, 8 Mar 2020 at 15:31, stan wrote:
> > >
> > > > On Sun, Mar 08, 2020 at 10:29:09AM -0400, stan wrote:
> > > > > Still working on updateable views.
> > > > >
> > > > > Wish list item, a way to see the entire query that caused the
> > trigger to
> > > > > fire.
> > > > >
> > > > > Now on to something i hope I can get. Can I see what the verb that
> > caused
> > > > > the trigger to fire is? IE UPDATE, INSERT, DELETE?
> > > > >
> > > > > --
> > > > > "They that would give up essential liberty for temporary safety
> > deserve
> > > > > neither liberty nor safety."
> > > > > -- Benjamin Franklin
> > > > >
> > > > >
> > > >
> > > > OH, what was I thinking, that is controled by the trigger.
> > > >
> > > > --
> > > > "They that would give up essential liberty for temporary safety deserve
> > > > neither liberty nor safety."
> > > > -- Benjamin Franklin
> > > >
> > >
> > > Hi,
> > >
> > > Still, you can access that information in the TG_OP variable available in
> > > trigger functions because you can define triggers firing on
> > > multiple operation types.
> > > For instance this is an example from the Postgres documentation:
> > >
> > > CREATE TRIGGER emp_audit
> > > INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
> > > FOR EACH ROW EXECUTE FUNCTION update_emp_view();
> > >
> >
> > Thaks for the info.
> >
> > Could yoou clarify how to acces this variable:
> >
> > This does not work:
> >
> > elog( NOTICE, "For OP $_TD->{TG_OP}" )
> >
> > But this does work
> >
> > elog( NOTICE, "On Table $_TD->{table_name}" );
> > --
> > "They that would give up essential liberty for temporary safety deserve
> > neither liberty nor safety."
> > -- Benjamin Franklin
> >
>
> Hi,
>
> Sorry I didn't know you are using something different than plpgsql.
> You can check the actual syntax for your language(plperl I guess) here:
>
> https://www.postgresql.org/docs/11/server-programming.html
>
> Look for the "Trigger functions" section below the corresponding language.
>
Yes, it is not there, which explains why I did not see it.
I can execute an SQL queryy from within plperl, do you think that will get
this value?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Re: Table with many NULLS for indexed column yields strange query plan
On 2020-03-05 18:08:53 -0700, greigwise wrote:
> I have a query like this:
>
> SELECT "table1".* FROM "table1"
> INNER JOIN "table2" ON "table2"."table1_id" = "table1"."id"
> INNER JOIN "table3" ON "table3"."id" = "table2"."table3_id"
> WHERE "table3"."number" = ''
> AND ("table2"."type") IN ('Standard') ;
>
> table2 has a large number of NULLS in the column table3_id. There is an
> index on this column. Here is the result of explain analyze:
>
> Merge Join (cost=1001.20..4076.67 rows=17278 width=167) (actual
> time=284.918..300.167 rows=2244 loops=1)
>Merge Cond: (table2.table3_id = table3.id)
>-> Gather Merge (cost=1000.93..787825.78 rows=621995 width=175) (actual
> time=5.786..283.269 rows=64397 loops=1)
> Workers Planned: 4
> Workers Launched: 4
> -> Nested Loop (cost=0.87..712740.12 rows=155499 width=175)
> (actual time=0.091..102.708 rows=13107 loops=5)
>-> Parallel Index Scan using index_table2_on_table3_id on
> table2 (cost=0.43..489653.08 rows=155499 width=16) (actual
> time=0.027..22.327 rows=13107 loops=5)
> Filter: ((type)::text = 'Standard'::text)
It looks like it postgresql is scanning the index here to get the
entries in the right order for the merge join. It's strange that it
thinks this is a good strategy even though it has to visit every row in
the table (no index cond).
How is the selectivity of "type"? Would an index on that column help?
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
Re: Real application clustering in postgres.
On 2020-03-06 15:55:27 +0100, Laurenz Albe wrote: > On Fri, 2020-03-06 at 03:25 -0600, Ron wrote: > > > RAC is not really a high availability solution: because of the shared > > > storage, it has a sibgle point of failure. > > > > This is utter nonsense. Dual redundant storage controllers > > connected to disks in RAID-10 configurations have been around for at > > least 25 years. > > > > Oracle got it's clustering technology from DEC, and I know > > that works. Cluster members, storage controllers and disks have all > > gone down, while the database and application keep on humming along. > > I am not saying that it is buggy, it is limited by design. > > If you have mirrored disks, and you write junk (e.g, because of > a flaw in a fibre channel cable, something I have witnessed), > then you have two perfectly fine copies of the junk. I have certainly seen enterprise SAN boxes go down (or deliver corrupted data) because of controller or firmware problems or just because a second disk in a RAID-5 failed before the spare could be brought online. But to be fair, a master/slave setup a la patroni isn't immune against "writing junk" either: Not on the hardware level (either of the nodes may have faulty hardware, and you may not notice it until too late), and more importantly, not on the software level. An erroneus DML statement (because of a bug in the application, or because the user/admin made a mistake) will cause the same wrong data to be distributed to all nodes (of course this also applies to RAC). hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Determining the type of an obkect in plperl
On 2020-03-05 05:59:10 -0500, stan wrote:
> On Wed, Mar 04, 2020 at 05:09:19PM -0700, David G. Johnston wrote:
> > On Wed, Mar 4, 2020 at 4:21 PM stan wrote:
> > > I am in the process of writing a plperl function. In this function
> > > I need to compare the data in the NEW versus OLD structures. I am
> > > writing this as a generic subroutine, so I am looping through and
> > > comparing the 2 to see what is different. Problem is, that I need
> > > to know whether to us n != or a ne comparison.
> > >
> > > how can I determine what the data type of the value element is?
> > >
> >
> > Not up to speed on Perl but you basically want everything to be done using
> > string equality - can't you just use "ne" everywhere and not worry about
> > comparing numbers using string comparison logic? Might want to disabled
> > warnings...
[...]
>
> Since I am just looking for differences, this may work.
It should work. In fact it is probably the most robust method. Perl
scalars don't have a type like "int" or "string". They can be both (or
neither) at the same time. Also, a numeric comparison may not be the
right thing even for values which look like numbers:
Consider:
#!/usr/bin/perl
use v5.12;
use warnings;
use Scalar::Util qw(looks_like_number);
my $x = "123456789012345678901";
my $y = "123456789012345678902";
say "eq(1): ", $x eq $y;
if (looks_like_number($x) && looks_like_number($y)) {
say "==(2): ", $x == $y;
} else {
say "eq(2): ", $x eq $y;
}
say "eq(3): ", $x eq $y;
This will print
eq(1):
==(2): 1
eq(3):
on my system: The string comparisons correctly determine $x and $y to be
not equal, but the numeric comparison says they are equal (the two
numbers are too large to fit into a 64 bit unsigned int, so perl will
use a 64 bit float for the comparison which will cause both to be
rounded to 123456789012345683968).
(I included the second string comparison to check that the numeric
comparison didn' affect the values, which I wasn't sure of).
> Presently I am getting some warnings, so I think I need to deal with the
> types. I already dealt with the columns that return NULL, these are
> undefined in the Perl hash, so I have to test for their existence before
> attempting the compare.
What do you mean by "undefined in the hash"?
* The keys exist but the values are undef
* The keys don't exist
Those are not the same thing, and you use different functions to test
for them (defined() and exists() respectively).
(I think it should be the former, but I'm too lazy to test it myself)
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
Re: Advice request : simultaneous function/data updates on many databases
On 2020-03-04 14:42:01 -0800, Guyren Howe wrote: > On Mar 4, 2020, at 14:33 , Rory Campbell-Lange > wrote: > Essentially we wish to reduce the window where the frontend and backend > aren't synchronised. > > If we have (for example) 200 databases which each take 2 seconds to > update, a client could be on the wrong frontend code for over 6 minutes. > > > Send each of the servers a PL/PGSQL method that executes all the things in a > transaction and then waits until the same clock time to commit. Last time I looked, some DDL commands (especially "drop table") took an exclusive lock on the affected table. So you may want to keep transactions which execute such commands very short to prevent them from blocking other transactions for a noticeable amount of time. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: How to discover what table is
correct, what schema that table belongs to. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: How to discover what table is
On 3/8/20 12:26 PM, PegoraroF10 wrote: I have a publication/subscription replication. Then this week started to see this message on Log of replica server. Message is "duplicate key value violates unique constraint "pksyslookup"" Detail is "Key (lookup_id)=(56) already exists." and on production server message is "logical decoding found consistent point at 5D5/5CD64ED0" I have 200 schemas on that database and every schema has that table, so ho can I discover what table is having that violation ? Are you asking which *schema* it's in? -- Angular momentum makes the world go 'round.
How to discover what table is
I have a publication/subscription replication. Then this week started to see this message on Log of replica server. Message is "duplicate key value violates unique constraint "pksyslookup"" Detail is "Key (lookup_id)=(56) already exists." and on production server message is "logical decoding found consistent point at 5D5/5CD64ED0" I have 200 schemas on that database and every schema has that table, so ho can I discover what table is having that violation ? There is a way to know what WAL is being processed and get the command should run to discover what table should be updated ? -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Restrict user to create only one db with a specific name
On 2020-03-06 16:39:14 -0700, David G. Johnston wrote: > On Fri, Mar 6, 2020 at 4:28 PM Tiffany Thang wrote: > Is there a way in PostgreSQL 12 to restrict user to creating a database > with a specific database name? [...] > Why does userA need create database privileges? Not speaking for the OP, but: Some test frameworks (e.g. the one included in Django) like to create their own test database to ensure that it only contains the test data. So if you are using one of these as intended the user running the tests needs to be able to create and drop databases. If you are running a tests from multiple projects against the same cluster, it might be a good idea to ensure that each job can only create (and drop) their own test database and not those of other jobs (or - worse - the production database). hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Another INSTEAD OF TRIGGER question
On Sun, 8 Mar 2020 at 15:31, stan wrote: > On Sun, Mar 08, 2020 at 10:29:09AM -0400, stan wrote: > > Still working on updateable views. > > > > Wish list item, a way to see the entire query that caused the trigger to > > fire. > > > > Now on to something i hope I can get. Can I see what the verb that caused > > the trigger to fire is? IE UPDATE, INSERT, DELETE? > > > > -- > > "They that would give up essential liberty for temporary safety deserve > > neither liberty nor safety." > > -- Benjamin Franklin > > > > > > OH, what was I thinking, that is controled by the trigger. > > -- > "They that would give up essential liberty for temporary safety deserve > neither liberty nor safety." > -- Benjamin Franklin > Hi, Still, you can access that information in the TG_OP variable available in trigger functions because you can define triggers firing on multiple operation types. For instance this is an example from the Postgres documentation: CREATE TRIGGER emp_audit INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view FOR EACH ROW EXECUTE FUNCTION update_emp_view(); Regards, Sándor
Re: Another INSTEAD OF TRIGGER question
> On Mar 8, 2020, at 8:31 AM, stan wrote: > > On Sun, Mar 08, 2020 at 10:29:09AM -0400, stan wrote: >> Still working on updateable views. >> >> Wish list item, a way to see the entire query that caused the trigger to >> fire. >> >> Now on to something i hope I can get. Can I see what the verb that caused >> the trigger to fire is? IE UPDATE, INSERT, DELETE? >> >> -- >> "They that would give up essential liberty for temporary safety deserve >> neither liberty nor safety." >>-- Benjamin Franklin >> >> > > OH, what was I thinking, that is controled by the trigger. > > -- > "They that would give up essential liberty for temporary safety deserve > neither liberty nor safety." >-- Benjamin Franklin > Or that is what controls which trigger is fired. >
Re: Another INSTEAD OF TRIGGER question
On Sun, Mar 08, 2020 at 10:29:09AM -0400, stan wrote: > Still working on updateable views. > > Wish list item, a way to see the entire query that caused the trigger to > fire. > > Now on to something i hope I can get. Can I see what the verb that caused > the trigger to fire is? IE UPDATE, INSERT, DELETE? > > -- > "They that would give up essential liberty for temporary safety deserve > neither liberty nor safety." > -- Benjamin Franklin > > OH, what was I thinking, that is controled by the trigger. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Another INSTEAD OF TRIGGER question
Still working on updateable views. Wish list item, a way to see the entire query that caused the trigger to fire. Now on to something i hope I can get. Can I see what the verb that caused the trigger to fire is? IE UPDATE, INSERT, DELETE? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Restrict user to create only one db with a specific name
Hi Tiff, note that there is an absurd high maximum of databases possible inside a PostgreSQL database cluster. I think, the maximum is 4,294,950,911 but I'm sure you don't get that many users or databases anyway. ;-) Cheers, Paul > On 07. Mar, 2020, at 23:35, Tiffany Thang wrote: > > Thanks David and Paul. Because each user wants the ability to drop and > recreate their own databases, I want to impose a restriction on the database > names. I’ll just implement what Paul has suggested. > > Thanks. > > Tiff > > On Sat, Mar 7, 2020 at 2:35 AM Paul Förster wrote: > Hi Tiff, > > from what you say, it sounds that each user should have his or her own > database. > > Considering the other answers here already pointing out the difficulties, why > don't you just create a database for each user with the same name as the > username and grant him or her access to it. > > So, basically like this: > > postgres=# create role "userA" login; > CREATE ROLE > postgres=# create database "userA" owner "userA"; > CREATE DATABASE > postgres=# create role "userB" login; > CREATE ROLE > postgres=# create database "userB" owner "userB"; > CREATE DATABASE > > When, say, "userB" goes away, his or her data will go the way all things do: > > drop database "userB"; > drop role "userB"; > > Or did I misunderstand you? > > Cheers, > Paul > > > > On 07. Mar, 2020, at 00:28, Tiffany Thang wrote: > > > > Hi, > > Is there a way in PostgreSQL 12 to restrict user to creating a database > > with a specific database name? > > > > For example, userA can only create a database with a name called mydb. Any > > other names would generate an error. > > > > If that is not possible, will it be possible then to limit userA to > > creating only one database? Granting the createdb privilege would allow the > > user to create any number of databases which I want to avoid. > > > > Thanks. > > > > Tiff >
