Re: Performance problem postgresql 9.5
On 06/08/2018 03:56 PM, Miguel Angel Sanchez Sandoval wrote: > I see the querys active and encounter > select fun ('./ 2yhdgrfrt63788') Would it be possible (observing appropriate precautions for a compromised server) to report here the language and definition of any function(s) named 'fun' in that database? Effectively: select lanname, prosrc, probin from pg_proc join pg_language as l on l.oid = prolang where proname = 'fun'; Regards, -Chap
Re: Multiple PostgreSQL instances on one machine
On 06/08/2018 01:29 PM, Tony Sullivan wrote: I am trying to consolidate some machines in my server room particularly in the testing environment and I was hoping someone could point me in the right direction. I currently have three machines running PostgreSQL for testing purposes. Each week a backup is made of the production database and this is deployed onto these three machines. None of these machines is any where near capacity. I would like to host all three instances on one machine. I know that "initdb" can be used to create additional installations, but the part I am unsure about is the tablespace. The production database has a tablespace defined that resides on its SSD. When I use the production backup, I have to create a mount point on the test machine with the same name as the one on the production database. I am not certain how I would handle this situation if I am serving three separate instances of that database from one computer. From here: https://www.postgresql.org/docs/10/static/manage-ag-tablespaces.html "The directory $PGDATA/pg_tblspc contains symbolic links that point to each of the non-built-in tablespaces defined in the cluster. Although not recommended, it is possible to adjust the tablespace layout by hand by redefining these links. Under no circumstances perform this operation while the server is running. Note that in PostgreSQL 9.1 and earlier you will also need to update the pg_tablespace catalog with the new locations. (If you do not, pg_dump will continue to output the old tablespace locations.)" A quick test here showed that you can create a new directory and move the contents of the existing tablespace into it and then relink the tablespace to the new location. create tablespace tblspc_test location '/home/postgres/test_tblspc'; create table tblspc_table(id int) tablespace tblspc_test ; select * from pg_class where relname = 'tblspc_table'; -[ RECORD 1 ]---+- relname | tblspc_table relnamespace| 2200 reltype | 1836557 reloftype | 0 relowner| 10 relam | 0 relfilenode | 1836555 reltablespace | 1836554 So in $DATA/pg_tblspc: 1836554 -> /home/postgres/test_tblspc/ mkdir /home/postgres/tblspc_1 cd /home/postgres/test_tblspc/ cp -r PG_10_201707211/ ../tblspc_1/ shutdown Postgres cd $DATA/pg_tblspc rm 1836554 ln -s /home/postgres/tblspc_1 1836554 start Postgres select * from pg_class where relname = 'tblspc_table'; -[ RECORD 1 ]---+- relname | tblspc_table relnamespace| 2200 reltype | 1836557 reloftype | 0 relowner| 10 relam | 0 relfilenode | 1836555 reltablespace | 1836554 -- Adrian Klaver adrian.kla...@aklaver.com
Re: Multiple PostgreSQL instances on one machine
If they are just test environments, why a whole dedicated cluster per instance? Just give each a unique name for the database and run it all on one cluster. I'd also go back and reconsider why these are separate machines in the first place and make sure you're not violating any assumptions that were made. On Fri, Jun 8, 2018 at 4:29 PM, Tony Sullivan wrote: > I am trying to consolidate some machines in my server room particularly in > the testing environment and I was hoping someone could point me in the > right direction. > > I currently have three machines running PostgreSQL for testing purposes. > Each week a backup is made of the production database and this is deployed > onto these three machines. None of these machines is any where near > capacity. I would like to host all three instances on one machine. > > I know that "initdb" can be used to create additional installations, but > the part I am unsure about is the tablespace. The production database has > a tablespace defined that resides on its SSD. When I use the production > backup, I have to create a mount point on the test machine with the same > name as the one on the production database. I am not certain how I would > handle this situation if I am serving three separate instances of that > database from one computer. > > >
Multiple PostgreSQL instances on one machine
I am trying to consolidate some machines in my server room particularly in the testing environment and I was hoping someone could point me in the right direction. I currently have three machines running PostgreSQL for testing purposes. Each week a backup is made of the production database and this is deployed onto these three machines. None of these machines is any where near capacity. I would like to host all three instances on one machine. I know that "initdb" can be used to create additional installations, but the part I am unsure about is the tablespace. The production database has a tablespace defined that resides on its SSD. When I use the production backup, I have to create a mount point on the test machine with the same name as the one on the production database. I am not certain how I would handle this situation if I am serving three separate instances of that database from one computer.
Re: Performance problem postgresql 9.5
> On Jun 8, 2018, at 1:09 PM, Alvaro Herrera wrote: > > On 2018-Jun-08, Miguel Angel Sanchez Sandoval wrote: > >> Hi guys, migrate from 8.4 to 9.5, all OK except that 2-3 days pass and the >> database experiences slowness, I execute the linux top command and it shows >> me a postgres user process executing a strange command (2yhdgrfrt63788) >> that I consume a lot of CPU, I see the querys active and encounter select >> fun ('./ 2yhdgrfrt63788') , this did not happen in version 8.4, any help >> would appreciate it. > > Hmm, has your database been compromised? You may have an intruder there -- > beware. Definitely. The machine is compromised and doing Bad Things. Image it if possible; save the compromise payload you know about if not. Treat it as compromised and unsafe to attach to a network until you completely wipe and reinstall it. It's probably a compromise via postgresql open to the network with insecure settings. I've seen several of those reported recently, and this one is saving it's payload to the postgresql data directory - somewhere no other user or app will have access to, but which a compromised postgresql can easily write to. Check the pg_hba.conf and packet filter / firewall settings and see what the issue may be. Do the same checks on all your other postgresql servers, test and production. If there's a configuration mistake that let one server be compromised it's may well be there on others too. Unless you are positive the server was not attacked, don't trust it unless you can be absolutely certain it is clean. Best bet is to backup any critical data (and check it for trustworthiness), wipe and rebuild. Cheers, Steve
Re: Performance problem postgresql 9.5
On 2018-Jun-08, Miguel Angel Sanchez Sandoval wrote: > Hi guys, migrate from 8.4 to 9.5, all OK except that 2-3 days pass and the > database experiences slowness, I execute the linux top command and it shows > me a postgres user process executing a strange command (2yhdgrfrt63788) > that I consume a lot of CPU, I see the querys active and encounter select > fun ('./ 2yhdgrfrt63788') , this did not happen in version 8.4, any help > would appreciate it. Hmm, has your database been compromised? You may have an intruder there -- beware. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Performance problem postgresql 9.5
Could you please give more briefing about the queries executed. Let me know whether they are dml or ddl. Provide information like how long and from what time the queries are in running state, so that we can find a way to find the exact pain area. What is the size of the database?? When were the statistics gathered?? Regards, Pavan On Sat, Jun 9, 2018, 1:27 AM Miguel Angel Sanchez Sandoval < massan...@gmail.com> wrote: > > Hi guys, migrate from 8.4 to 9.5, all OK except that 2-3 days pass and the > database experiences slowness, I execute the linux top command and it shows > me a postgres user process executing a strange command (2yhdgrfrt63788) > that I consume a lot of CPU, I see the querys active and encounter select > fun ('./ 2yhdgrfrt63788') , this did not happen in version 8.4, any help > would appreciate it. > > regards > >
Performance problem postgresql 9.5
Hi guys, migrate from 8.4 to 9.5, all OK except that 2-3 days pass and the database experiences slowness, I execute the linux top command and it shows me a postgres user process executing a strange command (2yhdgrfrt63788) that I consume a lot of CPU, I see the querys active and encounter select fun ('./ 2yhdgrfrt63788') , this did not happen in version 8.4, any help would appreciate it. regards
Use index with x = ANY(array_column) expression
Hi all, Is there a way to speed up ANY-based array element search with some kind of index? I mean this: WHERE = ANY() Or is GIN index the only option with the @> operator? WHERE @> ARRAY[] Thank you, Otto
Re: ERROR: found multixact from before relminmxid
On Fri, Jun 8, 2018 at 1:08 PM Andres Freund wrote: > On 2018-06-08 12:38:03 -0500, Jeremy Finzel wrote: > > On Tue, Jun 5, 2018 at 8:42 PM, Alexandre Arruda > wrote: > > > > > Em seg, 28 de mai de 2018 às 16:44, Andres Freund > > > escreveu: > > > > > > > > Hi, > > > > > > > > I think I found the bug, and am about to post a fix for it belo > > > > > https://postgr.es/m/20180525203736.crkbg36muzxrj...@alap3.anarazel.de. > > > > > > > > Greetings, > > > > > > > > Andres Freund > > > > > > Hi Andres, > > > > > > In end of April we did a complete dump/reload in database to version > 10.3. > > > Today, the problem returns: > > > > > > production=# vacuum verbose co27t; > > > INFO: vacuuming "public.co27t" > > > ERROR: found multixact 81704071 from before relminmxid 107665371 > > > production=# vacuum full verbose co27t; > > > INFO: vacuuming "public.co27t" > > > ERROR: found multixact 105476076 from before relminmxid 107665371 > > > production=# cluster co27t; > > > ERROR: found multixact 105476076 from before relminmxid 107665371 > > > > > > But this time, regular vacuum versus full/cluster are different in > > > multixact number. > > > Your patch is applicable to this issue and is in 10.4 ? > > > > > > Best regards, > > > > > > Alexandre > > > > > > > > We encountered this issue ourselves for the first time on a busy OLTP > > system. It is at 9.6.8. We found that patching to 9.6.9 on a snapshot > of > > this system did not fix the problem, but I assume that is because the > patch > > in 9.6.9 only prevents the problem moving forward. Is that accurate? > > Which patch in 9.6.9 are you referring to? The patch I linked to above > hasn't yet been merged, much less been released. No I was referring to this from the documentation: - Avoid spuriously marking pages as all-visible (Dan Wood, Pavan Deolasee, Álvaro Herrera) This could happen if some tuples were locked (but not deleted). While queries would still function correctly, vacuum would normally ignore such pages, with the long-term effect that the tuples were never frozen. In recent releases this would eventually result in errors such as "found multixact n from before relminmxid n". thanks, Jeremy >
Re: ERROR: found multixact from before relminmxid
On 2018-06-08 12:38:03 -0500, Jeremy Finzel wrote: > On Tue, Jun 5, 2018 at 8:42 PM, Alexandre Arruda wrote: > > > Em seg, 28 de mai de 2018 às 16:44, Andres Freund > > escreveu: > > > > > > Hi, > > > > > > I think I found the bug, and am about to post a fix for it belo > > > https://postgr.es/m/20180525203736.crkbg36muzxrj...@alap3.anarazel.de. > > > > > > Greetings, > > > > > > Andres Freund > > > > Hi Andres, > > > > In end of April we did a complete dump/reload in database to version 10.3. > > Today, the problem returns: > > > > production=# vacuum verbose co27t; > > INFO: vacuuming "public.co27t" > > ERROR: found multixact 81704071 from before relminmxid 107665371 > > production=# vacuum full verbose co27t; > > INFO: vacuuming "public.co27t" > > ERROR: found multixact 105476076 from before relminmxid 107665371 > > production=# cluster co27t; > > ERROR: found multixact 105476076 from before relminmxid 107665371 > > > > But this time, regular vacuum versus full/cluster are different in > > multixact number. > > Your patch is applicable to this issue and is in 10.4 ? > > > > Best regards, > > > > Alexandre > > > > > We encountered this issue ourselves for the first time on a busy OLTP > system. It is at 9.6.8. We found that patching to 9.6.9 on a snapshot of > this system did not fix the problem, but I assume that is because the patch > in 9.6.9 only prevents the problem moving forward. Is that accurate? Which patch in 9.6.9 are you referring to? The patch I linked to above hasn't yet been merged, much less been released. Unfortunately, on second thought, I don't quite see how it applies to the cases here (rather than other reports about pg_authid and such). So there might be a separate issue. Is there any chance I could get access to a copy of the data? It's very hard to debug something like this without something that can reproduce the issue... Greetings, Andres Freund
Re: (2^63 - 1)::bigint => out of range? (because of the double precision)
Alexey Dokuchaev writes: > What is the rationale for (int ^ int) to return double precision rather > than numeric? I am missing something obvious here? There are two ^ operators, one taking float8 and one taking numeric. Since float8 is the preferred datatype in the numeric category (i.e. the top of the implicit-casting hierarchy), the float8 operator will be chosen unless one of the earlier disambiguation rules applies: https://www.postgresql.org/docs/current/static/typeconv-oper.html In this case, you need at least one input to be numeric, so that rule 3c fires before rule 3d can. You might argue that numeric should be the preferred type, but that falls foul of the SQL standard, which is quite clear that only numeric -> float8 can be an implicit cast, not the other direction. (They word it in terms of casts between exact and approximate numeric types, but that's the outcome.) The type resolution heuristics break down pretty badly if the preferred type in a category doesn't have implicit casts from every other type in the category. BTW, you could also just write 2.0^63 to get numeric. > P.S. On a tangentally related note, why is "NO CYCLE" is the default > for sequences? (a) the SQL standard says so, (b) most people tend to expect serial columns to not repeat values. > [*] Per documentation, "The [SQL] standard's AS expression > is not supported." Another "why is it so?" question, btw. ;-) We didn't get around to implementing that till v10. regards, tom lane
Re: ERROR: found multixact from before relminmxid
On Tue, Jun 5, 2018 at 8:42 PM, Alexandre Arruda wrote: > Em seg, 28 de mai de 2018 às 16:44, Andres Freund > escreveu: > > > > Hi, > > > > I think I found the bug, and am about to post a fix for it belo > > https://postgr.es/m/20180525203736.crkbg36muzxrj...@alap3.anarazel.de. > > > > Greetings, > > > > Andres Freund > > Hi Andres, > > In end of April we did a complete dump/reload in database to version 10.3. > Today, the problem returns: > > production=# vacuum verbose co27t; > INFO: vacuuming "public.co27t" > ERROR: found multixact 81704071 from before relminmxid 107665371 > production=# vacuum full verbose co27t; > INFO: vacuuming "public.co27t" > ERROR: found multixact 105476076 from before relminmxid 107665371 > production=# cluster co27t; > ERROR: found multixact 105476076 from before relminmxid 107665371 > > But this time, regular vacuum versus full/cluster are different in > multixact number. > Your patch is applicable to this issue and is in 10.4 ? > > Best regards, > > Alexandre > > We encountered this issue ourselves for the first time on a busy OLTP system. It is at 9.6.8. We found that patching to 9.6.9 on a snapshot of this system did not fix the problem, but I assume that is because the patch in 9.6.9 only prevents the problem moving forward. Is that accurate? Before we take an outage for this patch, we want as much information as possible on if this is indeed likely to be our issue. Like the other people on this thread, amcheck didn't show anything on the snap: db=# select bt_index_parent_check(indexrelid,true) FROM pg_stat_user_indexes WHERE relname = 'mytable'; bt_index_parent_check --- (5 rows) db=# select bt_index_check(indexrelid,true) FROM pg_stat_user_indexes WHERE relname = 'mytable'; bt_index_check (5 rows) Not surprisingly, I can get the problem to go away in production if I use pg_repack to rebuild the table. But we are interested of course in solving this problem permanently. Thanks, Jeremy
Re: (2^63 - 1)::bigint => out of range? (because of the double precision)
On 06/08/2018 10:23 AM, Alexey Dokuchaev wrote: Hi there, P.S. On a tangentally related note, why is "NO CYCLE" is the default for sequences? [*] Per documentation, "The [SQL] standard's AS expression is not supported." Another "why is it so?" question, btw. ;-) I found it. Its in the docs for 9.6-. That is not the case anymore in 10+: https://www.postgresql.org/docs/10/static/sql-createsequence.html "data_type The optional clause AS data_type specifies the data type of the sequence. Valid types are smallint, integer, and bigint. bigint is the default. The data type determines the default minimum and maximum values of the sequence. " -- Adrian Klaver adrian.kla...@aklaver.com
Re: (2^63 - 1)::bigint => out of range? (because of the double precision)
On 06/08/2018 10:23 AM, Alexey Dokuchaev wrote: Hi there, I've decided to run some tests to see how my tables' ids would survive when their yielding sequences would start hitting their MAXVALUE's, by doing some "SELECT setval('foo_id_seq', ~maxbigint)". As I don't like to hardcode numbers (esp. huge numbers, because sequences are always[*] bigint's), I've tried to use (2^63 - 1)::bigint as "maxbigint", to no Not always, bigints are just the default. All those cases where folks use the serial 'type' are getting an int sequence: create table serial_test2(id serial); \d serial_test2 Table "public.serial_test2" Column | Type | Collation | Nullable | Default +-+---+--+-- id | integer | | not null | nextval('serial_test2_id_seq'::regclass) \d+ serial_test2_id_seq Sequence "public.serial_test2_id_seq" Type | Start | Minimum | Maximum | Increment | Cycles? | Cache -+---+-++---+-+--- integer | 1 | 1 | 2147483647 | 1 | no | 1 Owned by: public.serial_test2.id avail, in contrast to (2^31 - 1)::int (-> below is short mnemonic for "returns"): select (2^31 - 1)::int -> 2147483647 (correct) select (2^63 - 1)::bigint-> bigint out of range (???) select (9223372036854775807)::bigint -> 9223372036854775807 (correct) Apparently, this is because the type of 2^63 is double precision, which is inexact; if I explicitly cast any of 2 or 63 to ::numeric, it behaves as expected: select (2::numeric^63 - 1)::bigint -> 9223372036854775807 (ok) select (2^63::numeric - 1)::bigint -> 9223372036854775807 (ditto) What is the rationale for (int ^ int) to return double precision rather than numeric? I am missing something obvious here? Not sure, someone else will have to explain. ./danfe P.S. On a tangentally related note, why is "NO CYCLE" is the default for sequences? My guess is because sequences are often used to provide numbers for a PRIMARY KEY and NO CYCLE is a heads up for key duplication before the PK code kicks in. [*] Per documentation, "The [SQL] standard's AS expression is not supported." Another "why is it so?" question, btw. ;-) Where in the docs are you seeing this? -- Adrian Klaver adrian.kla...@aklaver.com
(2^63 - 1)::bigint => out of range? (because of the double precision)
Hi there, I've decided to run some tests to see how my tables' ids would survive when their yielding sequences would start hitting their MAXVALUE's, by doing some "SELECT setval('foo_id_seq', ~maxbigint)". As I don't like to hardcode numbers (esp. huge numbers, because sequences are always[*] bigint's), I've tried to use (2^63 - 1)::bigint as "maxbigint", to no avail, in contrast to (2^31 - 1)::int (-> below is short mnemonic for "returns"): select (2^31 - 1)::int-> 2147483647 (correct) select (2^63 - 1)::bigint -> bigint out of range (???) select (9223372036854775807)::bigint -> 9223372036854775807 (correct) Apparently, this is because the type of 2^63 is double precision, which is inexact; if I explicitly cast any of 2 or 63 to ::numeric, it behaves as expected: select (2::numeric^63 - 1)::bigint-> 9223372036854775807 (ok) select (2^63::numeric - 1)::bigint-> 9223372036854775807 (ditto) What is the rationale for (int ^ int) to return double precision rather than numeric? I am missing something obvious here? ./danfe P.S. On a tangentally related note, why is "NO CYCLE" is the default for sequences? [*] Per documentation, "The [SQL] standard's AS expression is not supported." Another "why is it so?" question, btw. ;-)
Re: Query hitting empty tables taking 48 minutes
> On Jun 8, 2018, at 10:23 AM, David G. Johnston > wrote: > > Not sure what the right answer is but its seems your database (those tables > at least) are mis-configured for the workload being executed against them. > Significantly increasing the aggressiveness of the auto-vacuum process and/or > inserting manual vacuum analyze commands into your application at appropriate > times are probably necessary. > I’m fine with changing up table parameters, which is the option that would make sense for us (thanks for pointing that out). I have the auto vacuum threshold high because of other huge tables, and was not aware of the per table settings. I’ll use this excuse one time, I inherited this setup, now I own it :-) I’m concerned about a query that’s going against two tables that have had 300k entries in them (ie now empty and 2 entries) taking so long. Even if those tables where full, the query should of taken no time at all. The machine has 64GB memory, 12 physical cores (+12 hyper threads) and the storage is on a ZFS pool with 5 mirrored vdevs of 7.2k SAS drives. The entire db size is 2.63GB, easily fitting into memory. This is a production appliance, and is build to handle the load. Obviously needs some intelligent tuning though. nspname relname n_tup_ins n_tup_upd n_tup_del n_live_tup n_dead_tup reltuples av_thresholdlast_vacuum last_analyze av_needed pct_dead ds3 blob303498 2559303496 2 0 2 5000 2018-06-08 04:35:00.00 NULLfalse 0 ds3 job_entry 303659 815 303659 0 0 0 5000 2018-06-08 04:35:00.00 NULLfalse 0 Best, Robert
Re: Query hitting empty tables taking 48 minutes
On Fri, Jun 8, 2018 at 9:17 AM, Robert Creager wrote: > A nightly VACUUM FULL which ran based on heuristics resolved the problem. > This would seem to point to a db problem more than an app problem? I’m > unsure how the app could have an affect of this magnitude on the database, > although I’d love to be told otherwise. > Not sure what the right answer is but its seems your database (those tables at least) are mis-configured for the workload being executed against them. Significantly increasing the aggressiveness of the auto-vacuum process and/or inserting manual vacuum analyze commands into your application at appropriate times are probably necessary. David J.
Re: Query hitting empty tables taking 48 minutes
On Jun 7, 2018, at 4:58 PM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: I would suspect that vacuuming these tables would solve your problem. Whether there is an issue beyond a lack of vacuuming, or related to auto-vacuum, I am unsure. Though at this point it may take a vacuum full to recover back to a sane state. Though ANALYZE by itself should clear up the statistical discrepancy. A nightly VACUUM FULL which ran based on heuristics resolved the problem. This would seem to point to a db problem more than an app problem? I’m unsure how the app could have an affect of this magnitude on the database, although I’d love to be told otherwise. Best, Robert
Re: Code of Conduct plan
Justin Clift writes: > On 2018-06-08 09:46, Simon Riggs wrote: >> Would it not be better to consider arbitration as the first step in >> dispute resolution? > I'd probably leave it up to the CoC team/people to figure it out. :) Yeah, exactly. I don't think it's helpful for the document to try to micro-manage the committee's processes. If the committee isn't working in good faith, and effectively, to try to resolve disputes fairly then we have bigger problems. At that point you think about replacing the committee ... which *is* spelled out in the document. regards, tom lane
Re: Code of Conduct plan
On 06/07/2018 02:55 AM, Gavin Flower wrote: On 07/06/18 21:49, Raymond O'Donnell wrote: On 07/06/18 09:04, Pablo Hendrickx wrote: You don't have to be a magician to predict this is going to harm the community. Please keep your American social politics out of Postgres, thank you! As a long-time lurker and occasional participant on this list, I don't think this has ever been an issue, in my experience anyway. There might be an occasional turn of phrase which I have to parse a bit, but that's about it. :-) Ray. The Americans often seem to act as though most people lived in the USA, therefore we should all be bound by what they think is correct! Well the spate of privacy policy changes I have to deal with here(USA) brought on by actions of the EU would seem to contradict the above. Just an example of how all this flows in many directions. And please don't lump all Americans together as we come from many paths and often disagree on what is correct, which is what motivates my reservations about the CoC. Cheers, Gavin -- Adrian Klaver adrian.kla...@aklaver.com
Re: Can you make a simple view non-updatable?
On 06/08/2018 01:38 AM, Ryan Murphy wrote: Hello. I enjoy using VIEWs. Often my views are updatable, either automatically (due to being a simple 1-table view, or due to a TRIGGER). Sometimes they are meant to be just read-only. Is there any way to set a VIEW to be read-only -- specifically, can I do this for a view that is automatically updatable due to being simple? Using INSTEAD OF trigger?: create view ct_vw as select * from container; insert into ct_vw (c_id, cdesc, cell_per, c_size, c_units) values ('test', 'test container', 1, 2, 4); INSERT 1836533 1 CREATE OR REPLACE FUNCTION public.vw_ro() RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN RAISE NOTICE 'Read only view'; RETURN NULL; END; $function$ CREATE TRIGGER ro_trg INSTEAD OF INSERT or UPDATE or DELETE ON ct_vw FOR EACH ROW EXECUTE procedure vw_ro(); insert into ct_vw (c_id, cdesc, cell_per, c_size, c_units) values ('test', 'test container', 1, 2, 4); NOTICE: Read only view INSERT 0 0 update ct_vw set cell_per = 100 where c_id = '200PT'; NOTICE: Read only view UPDATE 0 delete from ct_vw where c_id = '200PT'; NOTICE: Read only view DELETE 0 The reason I want this: It will help me encode into my schema the distinction between views that are supposed to behave like full-fledged "subtypes" of a larger relation and need to be updatable, vs those that are merely a report / literally just a "view". Thanks! Ryan -- Adrian Klaver adrian.kla...@aklaver.com
Re: manipulating NUMERIC values in C extension
On Fri, 8 Jun 2018 at 13:44, John McKown wrote: > Have you considered the standard C library functions: "atoi()", "atof()", > "atol()", and "atoll()" ? Hi John My issue wasn't so much how to get a number out of the string, rather how to get that value back into a NUMERIC object to return back to the server. Thanks for taking the time to reply though, it's appreciated. Geoff
Re: manipulating NUMERIC values in C extension
On Fri, 8 Jun 2018 at 13:47, Geoff Winkless wrote: > Answering my own question, looks like And just in case anyone googling the question comes across this, this example code works. #include "postgres.h" #include #include "fmgr.h" #include "utils/geo_decls.h" #include "funcapi.h" #include "utils/array.h" #include "utils/builtins.h" #include "utils/numeric.h" #include "catalog/pg_type.h" PG_MODULE_MAGIC; PG_FUNCTION_INFO_V1(pgnumeric_x10); Datum pgnumeric_x10(PG_FUNCTION_ARGS) { Numeric v; char *r; char mybuff[1000]; double f; v=PG_GETARG_NUMERIC(0); r=numeric_normalize(v); f=atof(r)*10; sprintf(mybuff, "%f", f); v = DatumGetNumeric(DirectFunctionCall3(numeric_in, CStringGetDatum(mybuff), 0, -1)); pfree(r); PG_RETURN_NUMERIC(v); } Example of it running: =# CREATE OR REPLACE FUNCTION pgnumeric_x10(NUMERIC) RETURNS NUMERIC AS 'testpgnumchange.so', 'pgnumeric_x10' LANGUAGE C STRICT IMMUTABLE; CREATE FUNCTION Time: 0.811 ms =# select pgnumeric_x10(132387.4823487::NUMERIC); pgnumeric_x10 1323874.823487 (1 row) Time: 0.593 ms =# For obvious reasons I wouldn't suggest using atof on a numeric, we have our own functions for manipulating _Decimal128 which is what I'll actually be using in the end version, but this is easier to compile as an example :) Geoff
Re: manipulating NUMERIC values in C extension
On Fri, 8 Jun 2018 at 13:27, Geoff Winkless wrote: > numeric_in looks like it might do what I want but to do that I would > have to build a FunctionCallInfo struct to do that, and I'm not 100% > clear how to do that either :( Answering my own question, looks like res = DatumGetNumeric(DirectFunctionCall3(numeric_in, CStringGetDatum(buf), 0, -1)); should do it, judging from https://api.pgxn.org/src/orafce/orafce-3.6.1/convert.c Geoff
Re: manipulating NUMERIC values in C extension
On Fri, Jun 8, 2018 at 7:27 AM Geoff Winkless wrote: > Hi > > I'd like to be able to perform some manipulation on NUMERIC values in > a C function; however the exposed functionality in numeric.h is pretty > restrictive. > > I can see numeric_normalize will return a pointer to a string > representation, which is workable, and if there were an equivalent > string-to-numeric function that would be enough (although not the most > efficient) but I can't see a way to get a string back in to a numeric > value to return. > > numeric_in looks like it might do what I want but to do that I would > have to build a FunctionCallInfo struct to do that, and I'm not 100% > clear how to do that either :( > > I _could_ return the result as a varchar and cast it back to numeric > in the SQL, but that's not very clean. > > Accessing the numeric structure directly would work too but I'm > assuming that's not recommended since it's not exposed in numeric.h. > > Any thoughts would be appreciated, > Have you considered the standard C library functions: "atoi()", "atof()", "atol()", and "atoll()" ? > > Geoff > > -- Rap music is performed by those that can not sing so others can not think. Maranatha! <>< John McKown
manipulating NUMERIC values in C extension
Hi I'd like to be able to perform some manipulation on NUMERIC values in a C function; however the exposed functionality in numeric.h is pretty restrictive. I can see numeric_normalize will return a pointer to a string representation, which is workable, and if there were an equivalent string-to-numeric function that would be enough (although not the most efficient) but I can't see a way to get a string back in to a numeric value to return. numeric_in looks like it might do what I want but to do that I would have to build a FunctionCallInfo struct to do that, and I'm not 100% clear how to do that either :( I _could_ return the result as a varchar and cast it back to numeric in the SQL, but that's not very clean. Accessing the numeric structure directly would work too but I'm assuming that's not recommended since it's not exposed in numeric.h. Any thoughts would be appreciated, Geoff
Re: Code of Conduct plan
> On Jun 8, 2018, at 4:46 AM, Simon Riggs wrote: > > On 6 June 2018 at 19:22, Tom Lane wrote: >> I wrote: >>> Yeah, somebody else made a similar point upthread. I guess we felt that >>> the proper procedure was obvious given the structure, but maybe not. >>> I could support adding text to clarify this, perhaps along the line of >> >> Hmm ... actually, there's another special case that's not discussed, >> which is what happens if a committee or core member wants to file a >> complaint against someone else? They certainly shouldn't get to rule >> on their own complaint. So maybe change "complaint against" to >> "complaint by or against" in my proposed addition, and then we're good. > > Which brings up the further complication of in which order are things > dealt with? > > If people file complaints against each other. Is there benefit in > rushing to file a complaint? > > "The Committee will inform the complainant and the alleged violator of > their decision at that time." That is unclear. > > Are complaints considered AFTER information has been collected from > both parties? If so, it doesn't matter who complains first, both > parties will get their say. > > But if the person being complained about only hears of the complaint > after judgement has been made this means there is benefit in being the > first to complain, which will encourage people to complain early so > they can get their boot in first. And also cause double the volume of > complaints, since it will be necessary to counter-complain in order > for the alleged violator to get their say. Earlier it says: "With the cooperation of all parties, the Committee will aim to complete the investigation in a period of two weeks from the receipt of the complaint.” which I interpret as “The CoC committee will collect information in order to make a fair decision” which would involve talking to the alleged violator(s). Perhaps we need an additional line that says the CoC committee will be reaching out to all parties involved in a complaint, just to be clear? > Would it not be better to consider arbitration as the first step in > dispute resolution? Do we need judgement by a committee as the first > step? Do we even have time for judges to judge? I have noticed it is in the nature of our community for people to try and work things out amongst themselves first before escalating to others, or to take one another aside to try and work things out. For the minor issues that crop up (and I know “minor” is relative), I hope that remains the case. I view the CoC as being in place for having a way to report abusive behavior and harassment and knowing we will ensure our community is a safe, fun place to collaborate. Jonathan
Re: Code of Conduct plan
On Tue, 2018-06-05 at 12:06 -0400, James Keener wrote: > Do we need a code of conduct like this, or so we need a more general > dispute resolution process? Something that is public and aimed at > mediating disputes (even ones about bad conduct) and removing repeat > offenders. To be honest, larger issues of harassment should be handled > by the police. > > A code of conduct is basically "be excellent to each other", but what > that means is never going to be well codified in a document anyone can > produce. It's why we have a judiciary in the "real world". > > I don't participate too much here, but I've never see a group > implement a code of conduct go well. I'm a fairly socially liberal > person, but have been told in one group that my views as a cis, > hetero, white, middle class make aren't welcome in discussions about > getting more women or minorities to participate. Specifically there > was a discussion in that group about how since women often bare the > burden of child care, even when both partners work, that side projects > as a hiring criteria are sexist. I mentioned that as an involved > father I also find little time to work on side projects and that the > issue is more about those with kids than specifically women and was > essentially run out of the group. > > Another time, same group, someone was discussing guns, and someone > else said that this kind of discussion is why women don't participate > much. I mentioned that I know more women who own guns, hunt, and > target shoot than I do men who do that. I was again told to shut up > and banded for a few days when I pressed as to why a not-male-centric > discussion was being censored in the name of sexism and fairness. > > How will this CoC handle these situation? I obviously offended people > and had no intention of doing so. I was also told that the > moderators/CoC commitee would act fairly, and I obviously believe I > was mistreated by them. Forgive me for not believing in the > benevolence of the governors. > i think that's much broader problem of CoC that anyone would like to admit. but before i go further, let me introduce context of my personal view. i'm great fan of postgresql (although somewhat outside of my real work, i use it a lot for work and hobby) and that community, which i find really great. i wouldn't describe myself as an active community member, i'm mostly lurking (sometimes with significant delay) learning even more from other people's problems and solutions, and at times when i could be of help to someone it's too late (due to significant delays in reading). as of CoC, i would say i really do not care that much and it does not change my life a bit. but... there's always a "but". i personally hate formalizing everything for the idea of having all formalized. CoC in itself is political thing, for enforcing political correctness in many social, cultural, geographical, political, religious, intimate, and other aspects, all beyond community's interests. not only is prone to be abused, but implicitly invites ways of abusing to community's life. and generally (not saying anyone here personally) people demanding special treatment because of some CoC rules and people enforcing policing force of CoC in the name of political correctness, or for their personal needs of being part of, or contributing to that policing force may be more dangerous to community and other members than people who can very occasionally unintentionally offend someone. and does real harassment comes from unintentional offense? maybe, when the victim feels too much offended to try to understand what really happened. and than CoC becomes a tool to revenge, even more so when CoC is to punish offender, not really to mediate between involved parties. culture differences do not help in understanding each other when it comes that far. misunderstanding (involuntary or intentional (yes, that may happen)) is far more expected than intentional offense, that should be addressed and not political correctness. formalizing correctness is never good, helps nothing, introduces problems. creating entity for judging and punishing does not solve those newly introduced problems. wouldn't it be better if CoC didn't touch aspects beyond community's interests, only stated that friendliness is expected, some ways of mediation available and punishment only as a last resort solution? when technical community walks into keeping eyes on member's personal beliefs, feelings and way of life (like being too much polite, too much rude, too much humorous, too much fanatic, too much religious, or whatever) than that's not the same technical community anymore. just my 2c, ban me my dear community if i violated your CoC ;) > Jim > > On June 5, 2018 11:49:06 AM EDT, Benjamin Scherrey > wrote: > > > On Tue, Jun 5, 2018 at 10:37 PM, Peter Geoghegan > wrote: > It is of course possible that a member of the > committ
Re: Service pgpool
Dear all, Attached you can find my pgpoof.conf file. [root@pgpool02 pgpool-II-10]# pgpool -v pgpool-II version 3.7.3 (amefuriboshi) [root@asa-pgpool02 pgpool-II-10]# [root@pgpool02 pgpool-II-10]# cat /etc/redhat-release Red Hat Enterprise Linux Server release 7.5 (Maipo) [root@pgpool02 pgpool-II-10]# Thanks. 2018-06-08 0:10 GMT+02:00 Ahmed, Nawaz : > And also the contents of the unit file itself and some more details > whether is it the first time you are trying to get it work or it used to > work but suddenly stopped working etc. The contents of the unit file could > give a clue whether it was some typo or the sequence at which the different > directives are executed in a unit, target dependencies and so on. > > Best Regards, > > Nawaz Ahmed > Software Development Engineer > > Fujitsu Australia Software Technology Pty Ltd > 14 Rodborough Road, Frenchs Forest NSW 2086, Australia > T +61 2 9452 9027 > na...@fast.au.fujitsu.com > fastware.com.au > > > > > -Original Message- > From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] > Sent: Friday, 8 June 2018 4:30 AM > To: Joshua D. Drake ; Jean Claude < > jeancl...@gmail.com>; pgsql-general@lists.postgresql.org > Subject: Re: Service pgpool > > On 06/07/2018 11:25 AM, Joshua D. Drake wrote: > > On 06/07/2018 11:16 AM, Jean Claude wrote: > >> Hi all, > >> > >> below my problem about daemon : > >> > >> Jun 07 14:06:45 vm02 systemd[1]: Failed to start SYSV: Starts and > >> stops the pgpool daemon. > >> Jun 07 14:06:45 vm02 systemd[1]: Unit pgpool-II-10.service entered > >> failed state. > >> Jun 07 14:06:45 vm02 systemd[1]: pgpool-II-10.service failed. > >> > >> Can you help me? > > > > You probably want to ask on the pgpool list: > > > > https://pgpool.net/mediawiki/index.php/Mailing_lists > > > > If you do it would help to include the OS and its version as well as the > pgpool-II version. > > Looks to me like a start script using SYSV init in a systemd system and > systemd not liking it. > > > > >> > >> Thanks a lot > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > Disclaimer > > The information in this e-mail is confidential and may contain content > that is subject to copyright and/or is commercial-in-confidence and is > intended only for the use of the above named addressee. If you are not the > intended recipient, you are hereby notified that dissemination, copying or > use of the information is strictly prohibited. If you have received this > e-mail in error, please telephone Fujitsu Australia Software Technology Pty > Ltd on + 61 2 9452 9000 or by reply e-mail to the sender and delete the > document and all copies thereof. > > > Whereas Fujitsu Australia Software Technology Pty Ltd would not knowingly > transmit a virus within an email communication, it is the receiver’s > responsibility to scan all communication and any files attached for > computer viruses and other defects. Fujitsu Australia Software Technology > Pty Ltd does not accept liability for any loss or damage (whether direct, > indirect, consequential or economic) however caused, and whether by > negligence or otherwise, which may result directly or indirectly from this > communication or any files attached. > > > If you do not wish to receive commercial and/or marketing email messages > from Fujitsu Australia Software Technology Pty Ltd, please email > unsubscr...@fast.au.fujitsu.com > pgpool.conf Description: Binary data
Re: Can you make a simple view non-updatable?
Zitat von Ryan Murphy : I could see how I could revoke permissions from, say, all users that aren't superusers to INSERT or UPDATE certain views. However, if possible it would be nice to get an error message about the VIEW not being updatable, rather than a user access error, which could be misleading. I feel it would be the other way round. If you deny something that is technically possible, I would be puzzled to get an error about a technical impossibility. If you want to stop people updating a view, in my opion, it is very well to tell them they are not allowed to and not that it isn't technically possible. So there can start a discussion whether they are rightfully denied to update data therein, instead of first discussing why it is not technically possible to update to find out it IS technically possible but it was actually denied. When I try to insert into a non-updatable VIEWs, I get this message: ERROR: cannot insert into view "test_view" DETAIL: Views containing GROUP BY are not automatically updatable. HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule. It would be great to see something like this when trying to insert into a simple VIEW that I had made non-updatable: ERROR: cannot insert into view "test_view2" DETAIL: This view has manually been made non-updatable. You still could put a trigger on the views throwing according exceptions for specific users. I think I partly used that already but can't find the code at the moment. -- +49 (0)1578-772 37 37 +41 (0)78 947 36 21 Öffentlicher PGP-Schlüssel: http://pgp.mit.edu/pks/lookup?op=get&search=0xCA167FB0E717AFFC This message was sent using IMP, the Internet Messaging Program.
Re: Code of Conduct plan
On Fri, Jun 8, 2018 at 11:18 AM, Justin Clift wrote: > On 2018-06-08 09:46, Simon Riggs wrote: > > >> Would it not be better to consider arbitration as the first step in >> dispute resolution? >> > > This bit sounds like it'd need to be on a case-by-case basis. > > It's pretty easy to imagine scenarios where arbitration wouldn't be > appropriate. > > Whether or not they come about in the PG Community or not is a > different matter. > > My point being that arbitration isn't necessarily automatically the > right direction. > > I'd probably leave it up to the CoC team/people to figure it out. :) +1 If it were me I would just say that CoC has an obligation to try in good faith to resolve things in line with the common interest of an international community and leave it at that. > > > + Justin > > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more
Re: Service pgpool
Hi all, How I can resolved the error about the pgpool daemon? any idea? Jun 08 05:23:05 -pgpool02 pgpool[1400]: [5-1] 2018-06-08 05:23:05: pid 1400: LOG: setting the local watchdog node name to "- pgpool02.adm.cacc.ch:5432 Linux -pgpool02" Jun 08 05:23:05 -pgpool02 pgpool[1400]: [6-1] 2018-06-08 05:23:05: pid 1400: LOG: watchdog cluster is configured with 1 remote nodes Jun 08 05:23:05 -pgpool02 pgpool[1400]: [7-1] 2018-06-08 05:23:05: pid 1400: LOG: watchdog remote node:0 on -pgpool01.adm.cacc.ch:9000 Jun 08 05:23:05 -pgpool02 pgpool[1400]: [8-1] 2018-06-08 05:23:05: pid 1400: LOG: interface monitoring is disabled in watchdog Jun 08 05:23:05 -pgpool02 pgpool[1400]: [9-1] 2018-06-08 05:23:05: pid 1400: LOG: watchdog node state changed from [DEAD] to [LOADING] Jun 08 05:23:05 -pgpool02 pgpool[1400]: [10-1] 2018-06-08 05:23:05: pid 1400: LOG: new outbound connection to -pgpool01.adm.cacc.ch:9000 Jun 08 05:23:05 -pgpool02 pgpool[1400]: [11-1] 2018-06-08 05:23:05: pid 1400: FATAL: Add to watchdog cluster request is rejected by node "- pgpool01.adm.cacc.ch:9000" Jun 08 05:23:05 -pgpool02 pgpool[1400]: [11-2] 2018-06-08 05:23:05: pid 1400: HINT: check the watchdog configurations. Jun 08 05:23:05 -pgpool02 pgpool[1400]: [12-1] 2018-06-08 05:23:05: pid 1400: LOG: Watchdog is shutting down Jun 08 05:23:05 -pgpool02 systemd[1]: Removed slice User Slice of postgres. Jun 08 05:23:05 -pgpool02 systemd[1]: Stopping User Slice of postgres. Jun 08 05:23:07 -pgpool02 pgpool-II-10[1369]: Starting pgpool-II-10 service: [FAILED] Jun 08 05:23:07 -pgpool02 systemd[1]: pgpool-II-10.service: control process exited, code=exited status=1 Jun 08 05:23:07 -pgpool02 systemd[1]: Failed to start SYSV: Starts and stops the pgpool daemon. Jun 08 05:23:07 -pgpool02 systemd[1]: Unit pgpool-II-10.service entered failed state. Jun 08 05:23:07 -pgpool02 systemd[1]: pgpool-II-10.service failed. Jun 08 05:23:07 -pgpool02 polkitd[475]: Unregistered Authentication Agent for unix-process:1363:7104696 (system bus name :1.137, object path /org/freedesktop/PolicyKit1/AuthenticationAgent, locale en_US.UTF-8) (disconnected from bus) Jun 08 05:31:59 -pgpool02 sshd[1914]: Accepted publickey for root from 172.16.254.41 port 51817 ssh2: RSA SHA256:tI23LiyQq17BD1HA2CTjnRe10Ai2Gc/tykb3VxbUNL4 Jun 08 05:32:00 -pgpool02 systemd[1]: Started Session 50 of user root. Jun 08 05:32:00 -pgpool02 sshd[1914]: pam_unix(sshd:session): session opened for user root by (uid=0) Jun 08 05:32:00 -pgpool02 systemd-logind[479]: New session 50 of user root. Jun 08 05:32:00 -pgpool02 systemd[1]: Starting Session 50 of user root. Thanks for your help.
Re: Can you make a simple view non-updatable?
On 06/08/2018 04:17 AM, Ryan Murphy wrote: maybe it is time to overhaul the security concept. I could see how I could revoke permissions from, say, all users that aren't superusers to INSERT or UPDATE certain views. However, if possible it would be nice to get an error message about the VIEW not being updatable, rather than a user access error, which could be misleading. When I try to insert into a non-updatable VIEWs, I get this message: ERROR: cannot insert into view "test_view" DETAIL: Views containing GROUP BY are not automatically updatable. HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule. It would be great to see something like this when trying to insert into a simple VIEW that I had made non-updatable: ERROR: cannot insert into view "test_view2" DETAIL: This view has manually been made non-updatable. Something like CREATE READ ONLY VIEW test_view2 AS SELECT -- Angular momentum makes the world go 'round.
Re: Code of Conduct plan
On 2018-06-08 09:46, Simon Riggs wrote: Would it not be better to consider arbitration as the first step in dispute resolution? This bit sounds like it'd need to be on a case-by-case basis. It's pretty easy to imagine scenarios where arbitration wouldn't be appropriate. Whether or not they come about in the PG Community or not is a different matter. My point being that arbitration isn't necessarily automatically the right direction. I'd probably leave it up to the CoC team/people to figure it out. :) + Justin
Re: Can you make a simple view non-updatable?
> maybe it is time to overhaul the security concept. > I could see how I could revoke permissions from, say, all users that aren't superusers to INSERT or UPDATE certain views. However, if possible it would be nice to get an error message about the VIEW not being updatable, rather than a user access error, which could be misleading. When I try to insert into a non-updatable VIEWs, I get this message: ERROR: cannot insert into view "test_view" DETAIL: Views containing GROUP BY are not automatically updatable. HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule. It would be great to see something like this when trying to insert into a simple VIEW that I had made non-updatable: ERROR: cannot insert into view "test_view2" DETAIL: This view has manually been made non-updatable.
Re: Code of Conduct plan
On 6 June 2018 at 19:22, Tom Lane wrote: > I wrote: >> Yeah, somebody else made a similar point upthread. I guess we felt that >> the proper procedure was obvious given the structure, but maybe not. >> I could support adding text to clarify this, perhaps along the line of > > Hmm ... actually, there's another special case that's not discussed, > which is what happens if a committee or core member wants to file a > complaint against someone else? They certainly shouldn't get to rule > on their own complaint. So maybe change "complaint against" to > "complaint by or against" in my proposed addition, and then we're good. Which brings up the further complication of in which order are things dealt with? If people file complaints against each other. Is there benefit in rushing to file a complaint? "The Committee will inform the complainant and the alleged violator of their decision at that time." That is unclear. Are complaints considered AFTER information has been collected from both parties? If so, it doesn't matter who complains first, both parties will get their say. But if the person being complained about only hears of the complaint after judgement has been made this means there is benefit in being the first to complain, which will encourage people to complain early so they can get their boot in first. And also cause double the volume of complaints, since it will be necessary to counter-complain in order for the alleged violator to get their say. Would it not be better to consider arbitration as the first step in dispute resolution? Do we need judgement by a committee as the first step? Do we even have time for judges to judge? Thanks for working on this. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Can you make a simple view non-updatable?
Zitat von Ryan Murphy : Is there any way to set a VIEW to be read-only -- specifically, can I do this for a view that is automatically updatable due to being simple? Without saying anything about if this is directly possible, using different users with appropriate grants Comes to my mind, i. e. maybe it is time to overhaul the security concept. Cheer, Thiemo This message was sent using IMP, the Internet Messaging Program.
Can you make a simple view non-updatable?
Hello. I enjoy using VIEWs. Often my views are updatable, either automatically (due to being a simple 1-table view, or due to a TRIGGER). Sometimes they are meant to be just read-only. Is there any way to set a VIEW to be read-only -- specifically, can I do this for a view that is automatically updatable due to being simple? The reason I want this: It will help me encode into my schema the distinction between views that are supposed to behave like full-fledged "subtypes" of a larger relation and need to be updatable, vs those that are merely a report / literally just a "view". Thanks! Ryan