Re: Enforce primary key on every table during dev?
On 03/01/2018 11:47 AM, Daevor The Devoted wrote: >I can't stand synthetic keys. By their very nature, they're so purposelessly >arbitrary, and allow you to insert garbage into the >table. How does not having a ‘real’ PK allow you to insert garbage and a ‘real’ one prevent garbage? If there’s no natural PK, at least a synthetic one will allow very quick record identification if used. Martin.
Re: What is wrong with my pgadmin?
Using pgAdmin 4 2.0 on pg 9.6 and 10 I get div/0 error. On 05/03/2018, 14:22, "Adrian Klaver" wrote: On 03/05/2018 06:05 AM, Łukasz Jarych wrote: > Hi Guys, > > what is wrong with my pgAdmin? > > I am creating wrong query: > > select 1/0 but still this is executed! Well it will be executed, but it should throw an error. Can you confirm that running select 1/0; in psql does indeed throw an error: test=# select 1/0; ERROR: division by zero Also: 1) What version of pgAdmin4 are you running? 2) What version of Postgres is it running against? > > > > why? > > Best wishes, > Jacek > > -- Adrian Klaver adrian.kla...@aklaver.com
Circle and box intersect
PG10 Is there an operator to determine if a box and circle intersect? I can only see box && box and can use centre+rad and distance to calculate circle:circle. Thanks.
RE: Circle and box intersect
Cheers - I'd tried postGIS on 9.6 but had install issues. Installed first time on 10 :) -Original Message- From: Andreas Kretschmer [mailto:andr...@a-kretschmer.de] Sent: 16 March, 2018 11:46 AM To: pgsql-general@lists.postgresql.org Subject: Re: Circle and box intersect Am 16.03.2018 um 11:00 schrieb Martin Moore: > PG10 > > Is there an operator to determine if a box and circle intersect? > I can only see box && box and can use centre+rad and distance to > calculate circle:circle. > > Thanks. > > please don't hijack other mail-threads by answering & changing the subject, your mail still contains references to "SELECT .. FOR UPDATE: find out who locked a row" Create a new mail for a new thread! to your question: consider PostGIS and it's functions st_intersects() Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Replication causing publisher node to use excessive cpu over time
I’ve got two Postgres 10/Debian stretch systems and have added a publish/subscribe on a single table that isn’t updated very often. The subscriber node is doing very little else. After a few days, it’s noticeable that the Postgres on the publisher node is constantly using a lot of cpu (26% today) and having a big impact on the system performance even when doing very little. Logging on to the subscriber and removing the subscription sees this value drop to an expect small value. I can’t see that this can be anything but a bug, but happy for any thoughts :) Martin Moore
Re: Replication causing publisher node to use excessive cpu over time
OK, have installed perf and will report back when the problem gets noticeable. Martin. On 04/12/2017, 12:40, "Michael Paquier" wrote: On Mon, Dec 4, 2017 at 9:03 PM, Martin Moore wrote: > After a few days, it’s noticeable that the Postgres on the publisher node is constantly using a lot of cpu (26% today) and having a big impact on the system performance even when doing very little. Logging on to the subscriber and removing the subscription sees this value drop to an expect small value. When debugging such issues, it is critical to know where the resources are spent, and you are giving no information that can help in understanding where CPU cycles are spent. You can do such measurements by using perf for example. -- Michael
Re: Find duplicates in a column then print Information to a file
From: Sherman Willden Date: Sunday, 31 December 2017 at 18:19 To: Subject: Find duplicates in a column then print Information to a file Development Platform: Ubuntu 17.10 mainly command line work Tools: perl 5.26 and postgresql 9.6 Goal: Display duplicate aria titles on screen and to a local file Database name: arias Table name: aria_precis csv delimiter: the # symbol arias=# \d aria_precis Table "public.aria_precis" Column| Type | Modifiers -+--+--- id | text | not null aria| text | artist | text | a_artist| text | album_title | text | Indexes: "aria_precis_pkey" PRIMARY KEY, btree (id) Problems: 1. Can't connect aria title to id 2. Can't write discovered information to file I know about this link but I probably didn't understand what I read there. https://www.postgresql.org/docs/current/static/sql-select.html#SQL-WITH What I know displays the 46 duplicate aria titles of 413 entries but not the id or artist: SELECT aria FROM aria_precis WHERE aria IN (SELECT aria FROM aria_precis GROUP BY aria HAVING COUNT(aria)>1); When I perform the following I get (0 rows): SELECT aria FROM aria_precis WHERE aria IN (SELECT aria FROM aria_precis GROUP BY id, aria HAVING COUNT(aria)>1); aria -- (0 rows) After I get the above information how do I write that information to a file? The only thing I know writes the entire database to a file: \COPY aria_precis TO '/home/sherman/aria_precis_2.csv' WITH DELIMITER '#,' CSV HEADER; Thank you; Sherman On the cmdline just do: psql arias -c ‘SELECT aria FROM aria_precis WHERE aria IN (SELECT aria FROM aria_precis GROUP BY aria HAVING COUNT(aria)>1)‘ > outfile.txt Martin.
Slow system due to ReorderBufferGetTupleBuf?
Postgres v10 on Debian stretch I’m suffering from an occasionally very slow system. A few weeks ago someone mentioned using perf. I’ve installed this and caught the system during a slow period. It shows the following as the top cpu users: 9.09% postgres [.] ReorderBufferGetTupleBuf 6.14% postgres [.] ReorderBufferReturnChange When ReorderBufferReturnChange is no longer running: 14.35% postgres [.] ReorderBufferGetTupleBuf Can someone shed some light on this and advise how to prevent it reoccurring? Cheers, Martin.
Re: Slow system due to ReorderBufferGetTupleBuf?
On 01/01/2018, 17:45, "Peter Geoghegan" wrote: On Mon, Jan 1, 2018 at 8:56 AM, Martin Moore wrote: > Can someone shed some light on this and advise how to prevent it reoccurring? You're using v10, which has these two commits: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=58b25e98106dbe062cec0f3d31d64977bffaa4af https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9fab40ad32efa4038d19eaed975bb4c1713ccbc0 Unfortunately, per the commit message of the first commit, it doesn't look like the tuple allocator uses any new strategy, at least until this v11 commit: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a4ccc1cef5a04cc054af83bc4582a045d5232cb3 My guess is that that would make a noticeable difference, once v11 becomes available. Could you test this yourself by building from the master branch? -- Peter Geoghegan Thanks Peter. I don’t really want to go down that route for various reasons. There’s a task that copies ‘old’ rows to various old_ tables and then deletes from the main tables, then does a vaccum and analyse. Tables only have 20-30k rows. I’m guessing this may be the trigger for the problem so have changed the timing from every 20 mins to once in the middle of the night when things are quiet. Would this explain the problem? Martin.
Re: Slow system due to ReorderBufferGetTupleBuf?
On 02/01/2018, 12:09, "Martin Moore" wrote: On 01/01/2018, 17:45, "Peter Geoghegan" wrote: On Mon, Jan 1, 2018 at 8:56 AM, Martin Moore wrote: > Can someone shed some light on this and advise how to prevent it reoccurring? You're using v10, which has these two commits: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=58b25e98106dbe062cec0f3d31d64977bffaa4af https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9fab40ad32efa4038d19eaed975bb4c1713ccbc0 Unfortunately, per the commit message of the first commit, it doesn't look like the tuple allocator uses any new strategy, at least until this v11 commit: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a4ccc1cef5a04cc054af83bc4582a045d5232cb3 My guess is that that would make a noticeable difference, once v11 becomes available. Could you test this yourself by building from the master branch? -- Peter Geoghegan Thanks Peter. I don’t really want to go down that route for various reasons. There’s a task that copies ‘old’ rows to various old_ tables and then deletes from the main tables, then does a vaccum and analyse. Tables only have 20-30k rows. I’m guessing this may be the trigger for the problem so have changed the timing from every 20 mins to once in the middle of the night when things are quiet. Would this explain the problem? Martin. == Having stopped the suspect task, I’m still getting the same problem. Can’t even stop postgres: waiting for server to shut down... failed pg_ctl: server does not shut down We’ve spent 2 yrs and a chunk of cash on a total system redesign and this is going to stop it from being released. Can someone give me an idea what may be causing this – and what ReorderBufferGetTupleBuf is actually doing in case it gives me a clue. Thanks.
Changing locale/charset
I created a 10.1 cluster on Debian using UTF8. I’d like to convert it to LATIN1, but am having various issues. So, it’s probably easiest to start again (I have a dump of the DB). To ensure I get it right, what is the correct way to create a cluster with LATIN1 encoding, how to remove the existing cluster and how to ensure I have the right settings for LOCALE etc. in the OS? Cheers.
Re: postgres for production
How much data do you have? That’s a fundamental thing to know. Martin. From: Azimuddin Mohammed Date: Wednesday, 31 January 2018 at 16:24 To: , Subject: postgres for production Hello, I have a question for postgres hardware requirement for production installation. My org want to use Vms with only 8 cores max + 64Gb RAM max. I have 4 database out of which 1 Db is write extensive 1 db is read extensive and moderate write and 2 other DBs are read with write one. can someone suggest me is this a good configuration with respect to hardware ? Thanks in Advance. -- Regards, Azim Virus-free. www.avast.com
Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later
I’m no expert but I’d think it unlikely an index would be considered for a table with only 100 rows in. Also I’m pretty sure only one index per table is used, so you’d want to put state1 and state2 in one index. You may wish to consider normalising too – so any field with a 1 or 2 at the end is moved to a separate table linked by gid. This would also help the indexing. Martin. From: Alexander Farber Date: Wednesday, 21 February 2018 at 12:16 To: pgsql-general Subject: Not sure if I should CREATE INDEX for text columns on which I plan to filter later Hello, in a 2 player game I store all games in the following PostgreSQL 10.2 table: CREATE TABLE words_games ( gid SERIAL PRIMARY KEY, created timestamptz NOT NULL, finished timestamptz, player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL CHECK (player1 <> player2), player2 integer REFERENCES words_users(uid) ON DELETE CASCADE, played1 timestamptz, played2 timestamptz, state1 text, -- tie, winning, losing, draw, won, lost state2 text, -- tie, winning, losing, draw, won, lost reason text, -- regular, resigned, expired, banned score1 integer NOT NULL CHECK (score1 >= 0), score2 integer NOT NULL CHECK (score2 >= 0), hand1 char[7] NOT NULL, hand2 char[7] NOT NULL, pilechar[116] NOT NULL, letters char[15][15] NOT NULL, values integer[15][15] NOT NULL, bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE ); In the 3 text columns state1, state2 and reason I store all possible game/player states. For example: player1 has failed to make her move in time, so that would result in: state1 = 'lost', state2 = 'won', reason = 'expired', On an advice I've got from this mailing list I am explicitly not using enums (in case I need to add unforseen states). The purpose of these 3 text columns is for me to display player stats later, by quering the columns. As you can imagine, mostly I perform SELECT on the words_games table - to send update to the game clients (PC and mobile). And in more seldom cases I update these 3 text columns - when a move is performed or a game gets finished or expires. My question please: Should I add the 3 indices as in: CREATE INDEX words_games_state1_index on words_games(state1); CREATE INDEX words_games_state2_index on words_games(state2); CREATE INDEX words_games_reason_index on words_games(reason); I am asking, because as an unexperienced database user I fail to see any difference when I run EXPLAIN: words=> select gid, state1, state2 from words_games where state1='won' or state2='won'; gid | state1 | state2 -++ 146 | lost | won 144 | lost | won 145 | lost | won 150 | won| lost .. 256 | won| lost 255 | won| lost 35 | lost | won (100 rows) words=> explain select gid, state1, state2 from words_games where state1='won' or state2='won'; QUERY PLAN --- Seq Scan on words_games (cost=0.00..109.85 rows=96 width=12) Filter: ((state1 = 'won'::text) OR (state2 = 'won'::text)) (2 rows) words=> CREATE INDEX words_games_state1_index on words_games(state1); CREATE INDEX words=> CREATE INDEX words_games_state2_index on words_games(state2); CREATE INDEX words=> explain select gid, state1, state2 from words_games where state1='won' or state2='won'; QUERY PLAN --- Seq Scan on words_games (cost=0.00..109.85 rows=96 width=12) Filter: ((state1 = 'won'::text) OR (state2 = 'won'::text)) (2 rows) Thank you for any insights Alex
Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later
My point was that your explain would be the same with indexes as without as they won’t be used. Martin. From: Alexander Farber Date: Wednesday, 21 February 2018 at 12:33 Cc: pgsql-general Subject: Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later Hi Martin - On Wed, Feb 21, 2018 at 1:26 PM, Martin Moore wrote: I’m no expert but I’d think it unlikely an index would be considered for a table with only 100 rows in. Also I’m pretty sure only one index per table is used, so you’d want to put state1 and state2 in one index. I hope to have more records in the words_games table later when my game is launched (currently in soft launch/beta). Regards Alex
Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later
>However, Martin's other comment about only using a single index is > incorrect. > Postgres can use multiple indexes per query, so it's often good practace to > put indexes on every column that might ever be used in a WHERE clause. > -- > Bill Moran That's very useful to know! Martin.
