[GENERAL] Limiting DB access by role after initial connection?

Hi. As context, I'm working with an organization with a current production database. Organizations in other locations using the same service delivery model want to share this database, with some but not all of the data restricted so that people at each site can see only that site's data. I've

Re: [GENERAL] Function with limit and offset - PostgreSQL 9.3

On Thursday, June 8, 2017, marcinha rocha > wrote: On my original select, the row will have migrated = false. Maybe All I need to put is a limit 2000 and the query will do the rest? You shoud try to avoid the for

[GENERAL] Function with limit and offset - PostgreSQL 9.3

Hi guys! I have the following queries, which will basically select data, insert it onto a new table and update a column on the original table. CREATE or REPLACE FUNCTION migrate_data() RETURNS integer; declare row record; BEGIN FOR row IN EXECUTE ' SELECT id

Re: [GENERAL] Function with limit and offset - PostgreSQL 9.3

On Thursday, June 8, 2017, marcinha rocha wrote: > On Thursday, June 8, 2017, marcinha rocha hotmail.com > > > wrote: > >> On my original select, the row will have

Re: [GENERAL] Function with limit and offset - PostgreSQL 9.3

On Thursday, June 8, 2017, marcinha rocha wrote: > When I call the function, it must execute 2000 rows and then stop. Then > when calling it again, it must start from 2001 to 4000, and so on > > You can do this is with plain sql with the help of a CTE. Insert

Re: [GENERAL] How does BDR replicate changes among nodes in a BDR group

On 9 June 2017 at 02:47, Zhu, Joshua wrote: > Thanks for the clarification. > > A follow up question, then, given *once joined all nodes are equal*, is that: > > should the node A dies or taken out of the group, the remaining three node > group (with B, C and D) would

Re: [GENERAL] Function with limit and offset - PostgreSQL 9.3

On Thursday, June 8, 2017, marcinha rocha wrote: > On my original select, the row will have migrated = false. Maybe All I > need to put is a limit 2000 and the query will do the rest? > > You shoud try to avoid the for loop, but yes a limit 2000 on the for loop

Re: [GENERAL] Function with limit and offset - PostgreSQL 9.3

On 6/8/2017 6:36 PM, marcinha rocha wrote: |UPDATEtablea a SETmigrated =yes WHEREa.id =row.id;| On my original select, the row will have migrated = false. Maybe All I need to put is a limit 2000 and the query will do the rest? SELECT does not return data in any determinate order unless you

Re: [GENERAL] Function with limit and offset - PostgreSQL 9.3

On 6/8/2017 5:53 PM, marcinha rocha wrote: Hi guys! I have the following queries, which will basically select data, insert it onto a new table and update a column on the original table. I'm sure your example is a gross simplification of what you're really doing, but if that's really all

Re: [GENERAL] Function with limit and offset - PostgreSQL 9.3

On 6/8/2017 5:53 PM, marcinha rocha wrote: > Hi guys! I have the following queries, which will basically select > data, insert it onto a new table and update a column on the original > table. I'm sure your example is a gross simplification of what you're really doing, but if that's really all

Re: [GENERAL] Redo the filenode link in tablespace

On 06/07/2017 06:05 PM, tel medola wrote: Holy shit! (sorry) Thanks, thanks!!! It worked! My goodness After I point to the filnode, I did a reindex on the toast and some records have already been located. Great, glad that it worked. Just realize we have been modifying a system

Re: [GENERAL] Redo the filenode link in tablespace

On Thu, Jun 08, 2017 at 08:11:30AM -0300, tel medola wrote: > Sure! > It's going to be a little long, That's the point :-) That way, people of the future can benefit from Adrian's excellent effort. Thanks, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD

Re: [GENERAL] Redo the filenode link in tablespace

Hello guys. I would like to thank Adrian very much for his great help and patience. Without your help, most likely I would be looking for another job now, thank you very much !!! Thanks to the database being Postgres and the community being so strong and united, everything worked out in the end.

Re: [GENERAL] Redo the filenode link in tablespace

Sure! It's going to be a little long, but I write with great pleasure. 2017-06-08 8:04 GMT-03:00 Karsten Hilbert : > On Thu, Jun 08, 2017 at 07:53:01AM -0300, tel medola wrote: > > > I would like to thank Adrian very much for his great help and patience. > > Without your

Re: [GENERAL] Redo the filenode link in tablespace

On Thu, Jun 08, 2017 at 07:53:01AM -0300, tel medola wrote: > I would like to thank Adrian very much for his great help and patience. > Without your help, most likely I would be looking for another job now, > thank you very much !!! > > Thanks to the database being Postgres and the community

Re: [GENERAL] pg_catalog tables don't have constraints?

> The bigger picture here is that catalog changes are supposed to be > executed by C code in response to DDL commands, and it's the C code > that is charged with maintaining catalog consistency. Constraints > would be useful if we supported updating the catalogs with direct > SQL manipulations;

Re: [GENERAL] Performance issue with Pointcloud extension

> Note that the execution time is 46 ms when the query is wrapped in an > explain analyze (while it's 3 s when it's not!) Actually, it seems to me that the performance issue is not on the query itself, it is on the fetching of the data returned by the query. Which explains why the query is fast

Re: [GENERAL] How does BDR replicate changes among nodes in a BDR group

Thanks for the clarification. A follow up question, then, given *once joined all nodes are equal*, is that: should the node A dies or taken out of the group, the remaining three node group (with B, C and D) would continue to function properly, correct? [somewhere I saw the term "downstream"

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

Harry Ambrose writes: > Please find the jar attached (renamed with a .txt extension as I know some > email services deem jars a security issue). Hmm, the output from this script reminds me quite a lot of one I was sent in connection with bug #1 awhile back:

[GENERAL] Db backup

A windows 7 computer went bad but the hdd is still operable. Is there a way to transfer the db data directories to another computer or does it require a dump and restore? -- [image: Innerspace Strategies] Jimmy Lunceford / HVAC Controls

Re: [GENERAL] Db backup

On 06/08/2017 12:10 PM, Jimmy Lunceford wrote: A windows 7 computer went bad but the hdd is still operable. Is there a way to transfer the db data directories to another computer or does it require a dump and restore? As long as the data is intact and you use the same major version of

Re: [GENERAL] Performance issue with Pointcloud extension

Eric Lemoine writes: > The initial "select pc_typmod_pcid(1)" query completely screws the > connection. > "select pc_typmod_pcid(1)" is just an example of a simple query that > triggers the problem. There are many others. But it has to be a query > using the Pointcloud

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

Harry writes: > The second vacuum causes an ERROR identical to that you are reporting > below (unexpected chunk number n (expected n) for toast value...). > However it may take up to ten attempts to replicate it. Interesting. > Out of interest, are you using any tablespaces other than

Re: [GENERAL] Performance issue with Pointcloud extension

On 06/08/2017 09:00 AM, Eric Lemoine wrote: Hi We have a rather strange performance issue with the Pointcloud extension [*]. The issue/bug may be in the extension, but we don't know for sure at this point. I'm writing to the list to hopefully get some guidance on how to further debug this. [*]

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

On 06/08/2017 08:13 AM, ADSJ (Adam Sjøgren) wrote: Achilleas writes: Anyone has a handy little script lying around? http://www.databasesoup.com/2013/10/de-corrupting-toast-tables.html Thanks for the response! Adam -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via

Re: [GENERAL] Weirdness with "not in" query

Wow. That is exactly it. Thank you. I really would not have expected there to be NULLs in that field. Geez. -- View this message in context: http://www.postgresql-archive.org/Weirdness-with-not-in-query-tp5965573p5965576.html Sent from the PostgreSQL - general mailing list archive at

Re: [GENERAL] Performance issue with Pointcloud extension

On 06/08/2017 09:20 AM, Eric Lemoine wrote: Looks like you also have postgis and pointcloud_postgis in mix. I would say this may get an answer sooner here: http://lists.osgeo.org/mailman/listinfo/pgpointcloud/ I am actually one of the developers of the Pointcloud extension. I haven't been

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

Achilleas writes: > First try to find which tables those toast relations refer to : > select 10919630::regclass , 10920100::regclass ; > Are those critical tables? Can you restore them somehow? They are our two big tables, containing the bulk of our data (one with 168M rows, the other with 320M

Re: [GENERAL] Weirdness with "not in" query

On 8 June 2017 at 17:27, greigwise wrote: > So, I'm using postgres version 9.6.3 on a mac and the results to this series > of queries seems very strange to me: > > db# select count(*) from table1 where id in > (1706302,1772130,1745499,1704077); > count > --- > 4 >

[GENERAL] Performance issue with Pointcloud extension

Hi We have a rather strange performance issue with the Pointcloud extension [*]. The issue/bug may be in the extension, but we don't know for sure at this point. I'm writing to the list to hopefully get some guidance on how to further debug this. [*]

[GENERAL] Weirdness with "not in" query

So, I'm using postgres version 9.6.3 on a mac and the results to this series of queries seems very strange to me: db# select count(*) from table1 where id in (1706302,1772130,1745499,1704077); count --- 4 (1 row) db# select count(*) from table2 where table1_id in

Re: [GENERAL] Weirdness with "not in" query

On 06/08/2017 08:27 AM, greigwise wrote: So, I'm using postgres version 9.6.3 on a mac and the results to this series of queries seems very strange to me: db# select count(*) from table1 where id in (1706302,1772130,1745499,1704077); count --- 4 (1 row) db# select count(*) from

Re: [GENERAL] Performance issue with Pointcloud extension

On Thu, 8 Jun 2017 18:00:04 +0200 Eric Lemoine wrote: > We have a rather strange performance issue with the Pointcloud extension > [*]. The issue/bug may be in the extension, but we don't know for sure > at this point. I'm writing to the list to hopefully get some

Re: [GENERAL] Performance issue with Pointcloud extension

> Looks like you also have postgis and pointcloud_postgis in mix. I would > say this may get an answer sooner here: > > http://lists.osgeo.org/mailman/listinfo/pgpointcloud/ I am actually one of the developers of the Pointcloud extension. I haven't been able to debug this up to now. -- Éric

Re: [GENERAL] Performance issue with Pointcloud extension

Il 08/06/2017 19:10, Eric Lemoine ha scritto: How can such a thing happen? Thanks for any insight on what could cause this. I'd try raising shared_buffers to 1 GB or something near 40% of the available memory If you run the query again, after getting bad results, what do you get? Cheers

Re: [GENERAL] Performance issue with Pointcloud extension

On 06/08/2017 07:27 PM, Moreno Andreo wrote: > Il 08/06/2017 19:10, Eric Lemoine ha scritto: >> >> How can such a thing happen? Thanks for any insight on what could cause >> this. >> >> > I'd try raising shared_buffers to 1 GB or something near 40% of the > available memory I tried to make it 4G,

Re: [GENERAL] Performance issue with Pointcloud extension

> Lots of missing information here ... > > Is there an index on public.sthelens.points? Yes, there are. lopocs=# \d sthelens; Table "public.sthelens" Column |Type| Modifiers

Re: [GENERAL] Performance issue with Pointcloud extension

>Have you experimented with other queries that don't involve PostGIS? >I'm wondering if your hook-installation code fails to work properly >unless PostGIS was loaded first. This would be easier to credit if >there are hooks both extensions try to get into. I think you're right on Tom. It

Re: [GENERAL] Db backup

On 6/8/2017 12:29 PM, John R Pierce wrote: On 6/8/2017 12:10 PM, Jimmy Lunceford wrote: A windows 7 computer went bad but the hdd is still operable. Is there a way to transfer the db data directories to another computer or does it require a dump and restore? the data directories should work

Re: [GENERAL] Db backup

On 6/8/2017 12:10 PM, Jimmy Lunceford wrote: A windows 7 computer went bad but the hdd is still operable. Is there a way to transfer the db data directories to another computer or does it require a dump and restore? the data directories should work as-is on another Windows computer with the