Re: PostgreSQL License

> On Sep 18, 2019, at 6:55 PM, Ron wrote: > >> On 9/18/19 6:03 PM, Ken Tanzer wrote: >> >> >>> On Wed, Sep 18, 2019 at 3:20 PM Ron wrote: >>> Charging for installing PostgreSQL is not the same as charging for >>> PostgreSQL. >>> >>> Bottom line: you charge for services you provide not for

Re: Web users as database users?

> On Sep 20, 2019, at 6:15 AM, Tim Clarke wrote: > >> On 20/09/2019 12:50, David Gallagher wrote: >> Hi - I’m working on a database that will be accessed via a web app. >> I’m used to a setup where there is one account/role that the app would >> use to access the database, but in my current sc

Re: citext, actually probably using extensions

> On Sep 20, 2019, at 1:12 PM, Tom Lane wrote: > > stan writes: >> But I must be doing something wrong. As the PG superuser, I did this: >> postgres=# CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public; >> CREATE EXTENSION > >> and as the superuser it shows up in the \dx display > >>

Re: Pg_auto_failover

> On Sep 24, 2019, at 10:22 AM, Sonam Sharma wrote: > > Can someone please help > > -- Forwarded message - > From: Sonam Sharma mailto:sonams1...@gmail.com>> > Date: Tue, Sep 24, 2019, 9:11 PM > Subject: Pg_auto_failover > To: pgsql-general

Re: Pg_auto_failover

> On Sep 24, 2019, at 9:41 AM, Sonam Sharma wrote: > > I was setting up pg_auto_failover. Have successfully set up and monitor and > primary instance. While setting up secondary it's failing with below : > > ERROR Failed to ensure empty directory "//backup" : Permission denied > Error Failed

Re: Pg_auto_failover

> On Sep 24, 2019, at 11:38 AM, Sonam Sharma wrote: > > Hi Rob, > Os : Linux rhel x86_64 > Postgres version : 11.2 > Installed the rpm of pg_auto_failover and then set up the monitor, primary. > Secondary is failing with given error > Well //backup is a valid reference to /backup (double sl

Re: managing primary key conflicts while restoring data to table with existing data

> On Sep 25, 2019, at 1:15 AM, Krishnakant Mane wrote: > > Hello all, > > I have been using postgresql for an enterprise quality account's automation > and inventory management software called GNUKhata > > Our team is planning to add backup and restore function in the s

Re: managing primary key conflicts while restoring data to table with existing data

> On Sep 25, 2019, at 8:24 AM, Krishnakant Mane wrote: > > >> On 25/09/19 7:50 PM, Adrian Klaver wrote: >>> On 9/25/19 12:15 AM, Krishnakant Mane wrote: >>> Hello all, >>> >>> I have been using postgresql for an enterprise quality account's automation >>> and inventory management sof

Re: managing primary key conflicts while restoring data to table with existing data

> On Sep 26, 2019, at 12:27 AM, Krishnakant Mane wrote: > > >> On 26/09/19 12:03 AM, Adrian Klaver wrote: >>> On 9/25/19 8:04 AM, Rob Sargent wrote: >>> >>> >>> On Sep 25, 2019, at 8:24 AM, Krishnakant Mane >> <mailto:kkm.

Re: managing primary key conflicts while restoring data to table with existing data

> On Sep 26, 2019, at 7:44 AM, Krishnakant Mane wrote: > > >> On 26/09/19 6:53 PM, Rob Sargent wrote: >> >> >> On Sep 26, 2019, at 12:27 AM, Krishnakant Mane wrote: >> >>> >>>> On 26/09/19 12:03 AM, Adri

Re: managing primary key conflicts while restoring data to table with existing data

-- Regards, Krishnakant Mane, Project Founder and Leader, GNUKhata //(Opensource Accounting, Billing and Inventory Management Software)// You might think about adding the new UUID column and use the existing primary key to inform the updates in dependent tables. Then remo

Re: row_to_json white space

On 9/26/19 11:54 AM, Mark Lybarger wrote: select ROW_to_json(q) from (select 'foo        a   bar' as TEST) q; Not here postgres=# select ROW_to_json(q) from (select 'foo    a bar' as TEST) q;   row_to_json ---  {"test":"foo    a   bar"} (1 row) po

Re: Thoughts on a cosntraint ?

> On Sep 29, 2019, at 1:52 PM, John W Higgins wrote: > > On Sun, Sep 29, 2019 at 12:40 PM stan > wrote: > I have a table that consists of 3 columns. > > vendor_key > mfg_key > preferred (boolean) > > The idea is that a given vendor is the vendor we want to use for eac

Re: Users, Roles and Connection Pooling

> On Oct 2, 2019, at 3:41 AM, Matt Andrews wrote: > > I have little experience in this area, but it seems like having a Postgres > role for every application user is the right way to do things. It’s just that > it also seems really inconvenient. > > For example how to map an application’s us

Re: Users, Roles and Connection Pooling

On 10/2/19 5:27 PM, raf wrote: I can't help with questions about scale but I like to give roles/users almost no permissions at all. i.e. They can't select, insert, update or delete anything. All they have permission to do is to execute stored functions that were installed by a role with the n

Re: BitmapAnd on correlated column?

On 10/3/19 3:22 PM, greigwise wrote: I'm running the following query on Postgres version 10.8: SELECT count(*) FROM test_table WHERE and id_column_1 IN (9954, 9690, 9689, 9688) AND id_column_2 IN (75328, 51448, 48060, 48065, 51803, 51449, 51802, 48064, - Sent from: https://www.postgresql-arc

Re: CVE-2018-1058

On 10/16/19 1:40 PM, Adrian Klaver wrote: On 10/14/19 3:27 PM, Lizeth Solis Aramayo wrote: Good afternoon, I am working with postgresql 9.6.15 and I need to restore in a 9.6.5 version,  I got an error, and  I found this page to install a patch What commands did you use to dump the 9.6.15 v

Re: DBD::Pg exorts char columns with trailing blanks

On 10/18/19 8:51 AM, Adrian Klaver wrote: On 10/18/19 7:42 AM, Matthias Apitz wrote: El día viernes, octubre 18, 2019 a las 03:01:58p. m. +0200, Tom Lane escribió: Matthias Apitz writes: When we export char columns with our Perl tools, they come out  with trailing blanks (in Sybase they d

Re: DBD::Pg exorts char columns with trailing blanks

On 10/18/19 9:19 AM, Adrian Klaver wrote: On 10/18/19 8:15 AM, Rob Sargent wrote: On 10/18/19 8:51 AM, Adrian Klaver wrote: On 10/18/19 7:42 AM, Matthias Apitz wrote: El día viernes, octubre 18, 2019 a las 03:01:58p. m. +0200, Tom Lane escribió: Matthias Apitz writes: When we export

Re: Composite type storage overhead

> On Oct 23, 2019, at 1:32 PM, Laiszner Tamás wrote: > > Hey there, > > I am currently exploring the options to utilize 128-bit numeric primary keys. > One of the options I am looking at is to store them as composites of two > 64-bit integers. > > The documentation page on composite types d

Re: Composite type storage overhead

can be done by simple (and supposedly efficient) bitwise operators when stored as integer, but becomes much more cumbersome with UUID, I guess. ---- *Feladó:* Rob Sargent *Elküldve:* 2019. október 23., szerda 22:58 *Cím

Re: SQL pretty pritner?

> On Oct 28, 2019, at 7:00 AM, Basques, Bob (CI-StPaul) > wrote: > > All, > > Take a look at the VI(m) editor. There is a Syntax mode for highlighting > different file types, as well as tools for exporting the highlighted text to > HTML. I have yet to find a file type that it doesn’t al

Re: SQL pretty pritner?

> On Oct 28, 2019, at 7:37 AM, Ron wrote: > > But does it reformat the text? > It’s an “indented text mode” so beeline respect current indentation. I don’t think it will take an stream out sql and add newlines at predictable places. Not at My desk to try. >> On 10/28/19 8:00 AM, Basques,

Re: DELETE Query Hang

> On Nov 12, 2019, at 3:47 PM, DrakoRod wrote: > > Hi folks! > > I Have a question, in a database are a table with many files (bytea) stored > (I support this database a don't design it), but we need delete many rows > (38000 rows approx), but I when execute query: > > BEGIN; > ALTER TABL

Re: DELETE Query Hang

> On Nov 12, 2019, at 4:01 PM, DrakoRod wrote: > > I understand, yep is a 9.5 version, Can I use query like this to execute > smaller batches?: > > DELETE FROM my_table > WHERE id IN ( >SELECT id >FROM logtable >LIMIT 10 > ); > > Thanks > I think you still want to qualify as yo

Re: Extract transactions from wals ??

> On Nov 21, 2019, at 9:35 AM, Marc Millas wrote: > > Hi Laurenz, > > > I was writing select from ""table"" as a template. We have to do this for a > bunch of tables. > So, to my understanding, what you suggest is to PITR up to the first > timestamp, extract all meaningfull tables, and then

Re: using replace function

> On Nov 28, 2019, at 1:09 PM, Antonio Silva wrote: > > Hello list > > I want to replace a string (not a substring) in a field but making sure that > the string in the full field. > > In the example below since 'blue' is not the full text in 'blue shark' there > should not have a replaceme

Re: Date created for tables

> If there's not enough time and motivation for the developers to implement > CREATED_ON and LAST_ALTERED in pg_class, then you should have said that in > the first place. We're adults; we understand that OSS projects have limited > resources, and won't go off and pout in the corner. > > Bu

Re: Date created for tables

> On Dec 24, 2019, at 11:48 AM, Ron wrote: > >  On 12/24/19 1:14 PM, Rob Sargent wrote: >> >>> If there's not enough time and motivation for the developers to implement >>> CREATED_ON and LAST_ALTERED in pg_class, then you should have said that in

Re: Syntax question about returning value from an insert

> On Dec 25, 2019, at 11:56 AM, Adrian Klaver < >> First of all, thanks to both of you for your fast response . >> Let me clarify. >> I have a table that records will be inserted into. Several of the columns >> in this table must be non NULL, and they are actually keys from other >> tables. Lik

Re: Syntax question about returning value from an insert

> On Dec 25, 2019, at 3:10 PM, stan wrote: > There is more that that. There is a project number, so the actuall key > represents the combination of project number, and cost category, Thire is a > constraint on T2 that assures that these combinations will be unique. > > When the 1st record for

Re: UPDATE many records

> On Jan 6, 2020, at 1:29 PM, Alban Hertroys wrote: > > I think you’re overcomplicating the matter. > > I’d just do it as a single update in one transaction. It’s only 50M rows. It > may take half an hour or so on decent hardware, depending on how > resource-intensive your function is. > I

Re: Worse performance with higher work_mem?

> On Jan 13, 2020, at 5:41 PM, Israel Brewster wrote: > >> On Jan 13, 2020, at 3:19 PM, Tom Lane > > wrote: >> >> Israel Brewster mailto:ijbrews...@alaska.edu>> >> writes: >>> In looking at the explain analyze output, I noticed that it had an >>> “external merge Di

Re: OID out of range

> On Jan 15, 2020, at 12:17 PM, Konireddy Rajashekar > wrote: > > @Alvaro Herrera, Can i alter the datatype of relid to oid and try ? > Not if Alvaro’s theory is correct. List that column, check for nulls, values greater the 2^32.

Re: jsonb_set() strictness considered harmful to data

> On Jan 17, 2020, at 4:28 PM, Ariadne Conill wrote: > > Hello, > > January 17, 2020 5:21 PM, "Tomas Vondra" > wrote: > > Thank you very much for coming together and finding a solution to this bug! > > Ariadne Let’s leave it at “issue” :)

Re: @@TRANCOUNT ?

1 ;) > On Jan 22, 2020, at 5:59 AM, İlyas Derse wrote: > >  > What's Prostgres' equivalent of select @@trancount? Do you have an idea ?

Re: Restore is failing

On 2/5/20 1:24 AM, Sonam Sharma wrote: I am copying schema data to qa box from prod.its failing and giving below error : WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction a

Re: Help : Removal of leading spaces in all the columns of a table

> On Feb 12, 2020, at 7:09 AM, Susan Hurst > wrote: > >  > I once wrote a trigger function to do just what you asked, however, it was a > huge drain on performance so I didn't use it for long, so I dropped the > trigger. Hopefully, someone has a more practical approach. I would be > inte

Re: How to fix 0xC0000005 exception in Postgres 9.0

> On Feb 22, 2020, at 4:28 PM, Ron wrote: > >  On 2/22/20 5:12 PM, Adrian Klaver wrote: >> On 2/22/20 2:39 PM, Andrus wrote: > [snip] >> This is a different issue and involves a product VFP that is EOL 5-10 years >> depending on support package. I'm going to say the hand writing is on the >

Re: a proposal for a new functionality: "SELECT * [EXCEPT col1 [,col2]]

> On Feb 25, 2020, at 2:14 PM, Tom Lane wrote: > > Paul Jungwirth writes: >> Not that this is necessarily fatal, but you'd need to avoid parsing >> trouble with the other EXCEPT, e.g. >> SELECT 1 EXCEPT SELECT 1; > > Yeah, it doesn't sound like much consideration has been given to > that

Re: Need to find the no. of connections for a database

On 2/27/20 10:38 PM, rob stone wrote: If the menu is built from a table in your database, then when "Processing report" starts you could set a flag (boolean) against those items so that if anybody tried to log-in or access those items, you could simply display a message along the lines of "Pro

Re: Determining the type of an obkect in plperl

> On Mar 5, 2020, at 4:22 AM, stan wrote: > > On Thu, Mar 05, 2020 at 12:27:12AM +, Ravi Krishna wrote: >> >>> >>> how can I determine what the data type of the value element is? >>> >> perl has a ref function which can tell what type of object. >> >> https://perldoc.perl.org/function

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 v

Re: plperl syntax question

> On Mar 12, 2020, at 4:34 PM, stan wrote: > > I am trying to run a query in plperl that uses a Perl variable from the > same function. > > Here is the code snippet: > > my $stmt = qq("SELECT employee_key from employee where id = '$user' ;"); > elog( NOTICE, "stmt = $stmt" ); > my $rv3 = spi

Re: plperl syntax question

> On Mar 12, 2020, at 7:44 PM, stan wrote: > > On Thu, Mar 12, 2020 at 06:37:02PM -0700, David G. Johnston wrote: >> On Thursday, March 12, 2020, stan wrote: >>> >>> my $rv3 = spi_exec_query('$stmt'); >>> What am I doing wrong here? >>> >> >> Putting a variable name into a single-quoted str

Re: Could postgres12 support millions of sequences? (like 10 million)

> On Mar 19, 2020, at 3:36 PM, pabloa98 wrote: > > Hello, > > My schema requires a counter for each combination of 2 values. Something like: > > CREATE TABLE counter( > group INT NOT NULL, > element INT NOT NULL, > seq_number INT NOT NULL default 0, > CONSTRAINT PRIMARY KEY (group, element)

Re: Could postgres12 support millions of sequences? (like 10 million)

> On Mar 19, 2020, at 4:13 PM, pabloa98 wrote: > > > > On Thu, Mar 19, 2020 at 2:50 PM Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: > > > > On Mar 19, 2020, at 3:36 PM, pabloa98 > <mailto:pablo...@gmail.com>> wrote: > > >

Re: Could postgres12 support millions of sequences? (like 10 million)

> On Mar 19, 2020, at 6:45 PM, pabloa98 wrote: > > > > On Thu, Mar 19, 2020 at 5:13 PM Adrian Klaver <mailto:adrian.kla...@aklaver.com>> wrote: > On 3/19/20 3:32 PM, pabloa98 wrote: > > > > > > On Thu, Mar 19, 2020 at 3:17 PM

Re: Could postgres12 support millions of sequences? (like 10 million)

> On Mar 19, 2020, at 7:35 PM, pabloa98 wrote: > > > > On Thu, Mar 19, 2020 at 6:16 PM Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: > > >> On Mar 19, 2020, at 6:45 PM, pabloa98 > <mailto:pablo...@gmail.com>> wrote: >> &g

Re: Could postgres12 support millions of sequences? (like 10 million)

> On Mar 20, 2020, at 4:59 PM, Peter J. Holzer wrote: > > On 2020-03-19 16:48:19 -0700, David G. Johnston wrote: >> First, it sounds like you care about there being no gaps in the records you >> end >> up saving. If that is the case then sequences will not work for you. > > I think (but I w

Re: Could postgres12 support millions of sequences? (like 10 million)

> On Mar 20, 2020, at 5:29 PM, Peter J. Holzer wrote: > >  >> >> >> OP has said small gaps are ok. > > Yes. This wasn't a response to the OP's requirements, but to David's > (rather knee-jerk, IMHO) "don't use sequences" response. Very often the > requirements which would preclude sequenc

Re: Could postgres12 support millions of sequences? (like 10 million)

> On Mar 21, 2020, at 10:47 AM, Adrian Klaver wrote: > > On 3/20/20 8:13 PM, pabloa98 wrote: >>Nothing I saw that said int could not become bigint. >> My bad. The code cannot be a bigint. Or it could be a bigint between 1 to >> :) > > > Aah, that was the counter Peter was talk

Re: Could postgres12 support millions of sequences? (like 10 million)

> On Mar 21, 2020, at 12:18 PM, pabloa98 wrote: > >  > >> Why? "Print" and "screen" forms have all sorts of practical restrictions >> like this. >> > Legacy I guess. These are all digital stuff. But the final result is an > identifier that people can read and realize what they are talking

Re: Could postgres12 support millions of sequences? (like 10 million)

> On Mar 21, 2020, at 1:13 PM, Peter J. Holzer wrote: > > On 2020-03-21 12:55:33 -0600, Rob Sargent wrote: >> To me the description of the ID smacks of database-in-the-name folly. I >> recognize that OP is likely unable to take another path. I’ll not push th

Re: Loading 500m json files to database

> On Mar 23, 2020, at 5:59 AM, Andrei Zhidenkov > wrote: > > Try to write a stored procedure (probably pl/python) that will accept an > array of JSON objects so it will be possible to load data in chunks (by > 100-1000 files) which should be faster. > >>> On 23. Mar 2020, at 12:49, Ertan

Re: Loading 500m json files to database

On 3/23/20 4:24 AM, pinker wrote: Hi, do you have maybe idea how to make loading process faster? I have 500 millions of json files (1 json per file) that I need to load to db. My test set is "only" 1 million files. What I came up with now is: time for i in datafiles/*; do psql -c "\copy

Re: Loading 500m json files to database

> On Mar 23, 2020, at 7:11 PM, David G. Johnston > wrote: > > On Mon, Mar 23, 2020 at 3:24 AM pinker > wrote: > time for i in datafiles/*; do > psql -c "\copy json_parts(json_data) FROM $i"& > done > > Don't know whether this is faster but it does avoid spinning up a

Re: Loading 500m json files to database

On 3/24/20 11:29 AM, Kevin Brannen wrote: From: pinker it's a cloud and no plpythonu extension avaiable unfortunately You're misunderstanding him. See David's post for an example, but the point was that you can control all of this from an *external* Perl, Python, Bash, whatever program

Re: Loading 500m json files to database

On 3/24/20 11:53 AM, Kevin Brannen wrote: From: Rob Sargent Sorry if I missed it, but have we seen the size range of these json files? Not that I've seen, but that's an implementation detail for whoever is doing the work. As someone else pointed out, pick the value as neede

Re: Ident authentication failed

> On Mar 26, 2020, at 1:43 PM, Ted To wrote: > > > > On 3/26/20 3:35 PM, Adrian Klaver wrote: >> Can you show the entire connection string used by miniflux? > The miniflux command is binary so, no, not easily. >> >> Did you reload/restart the server after making the changes below? > Yes. >

Re: too many clients already

> On Apr 2, 2020, at 9:06 AM, Abraham, Danny wrote: > > Hi, > > Will appreciate a hint here. > > Running on a big and stressed AIX platform and receiving lots of "CDTFATAL: > sorry, too many clients already" > and transient difficulty to log in. > > Happens on all PG versions (Tested 9.5,

Re: Backing out of privilege grants rabbit hole

> On Apr 3, 2020, at 2:50 AM, Laurenz Albe wrote: > > On Fri, 2020-04-03 at 00:59 -0400, AC Gomez wrote: >> Granted. But we are where we are, so I'm assuming this is going to be hand >> to hand combat. > > Yes, unfortunately there is no better way. > But DROP ROLE will produce messages that

Re: Improve COPY performance into table with indexes.

On 4/3/20 9:52 AM, Adrian Klaver wrote: On 4/2/20 9:42 PM, James Brauman wrote: I am using the COPY command to insert 10 million rows from a CSV file into a database table and I am finding the performance is unacceptable. When the COPY command is executed the disk I/O is extremely high which

Re: psql show me the : and ask user input, when running one sql file

> On Apr 5, 2020, at 8:24 AM, David G. Johnston > wrote: > >  >> On Sun, Apr 5, 2020 at 5:50 AM arden liu wrote: >> I am using psql to run this sql >> file(https://github.com/Arelle/Arelle/blob/master/arelle/plugin/xbrlDB/sql/public/xbrlPublicPostgresDB.ddl) >> here is my command: >> /usr/b

Re: Estimated resources for a 500 connections instance (VM)

On 4/7/20 2:23 PM, Sándor Daku wrote: On Tue, 7 Apr 2020 at 21:52, David Gauthier > wrote: After looking at some of the factors that can affect this, I think it may be important to know that most of the connections will be almost idle (in terms of i

Re: performance of first exec of prepared statement

On 4/16/20 6:15 PM, Adrian Klaver wrote: On 4/16/20 4:59 PM, Ted Toth wrote: On Thu, Apr 16, 2020 at 6:29 PM Ted Toth > wrote:     I've noticed that the first exec of an INSERT prepared statement     takes ~5 time longer (I'm using libpq in C and wrapping the calls t

Re: performance of first exec of prepared statement

> On Apr 17, 2020, at 7:34 AM, Ted Toth wrote: > >  > > >> On Fri, Apr 17, 2020 at 8:28 AM Ted Toth wrote: >> >> >>> On Thu, Apr 16, 2020 at 8:09 PM Rob Sargent wrote: >>> On 4/16/20 6:15 PM, Adrian Klaver wrote: >>> > On 4/

Re: Reg: Help to understand the source code

On 4/23/20 10:28 AM, Preethi S wrote: Hello Adrian, Thank you for the quick reply. This link is indeed helpful. This link explains how is a query processed. I am aware of how the query processing happens. In addition, I am looking for how the data processed, when data is inserted/modifie

Re: Could Not Connect To Server

On 4/23/20 1:12 PM, Dummy Account wrote: Hi David, When I backed-up, I don't know if the server was offline?  I can say that I was not running pgAdmin.  For instance, I backed up the Operating System and all of its applications.  If I go run other application, including other servers, they w

Re: Could Not Connect To Server

On 4/23/20 1:32 PM, Dummy Account wrote: Hey David, Can you tell me the exact name of the log file?  Then I can search for it. Thanks Apparently in your case it is called "log".  I don't have version 12, but  version 9's start up name "pg_log" and that is the name of the file in the data dir

Re: Fw: Re: Could Not Connect To Server

On 4/23/20 3:26 PM, Adrian Klaver wrote: On 4/23/20 1:37 PM, Dummy Account wrote:  From the Finder, I think that is the same a File Explorer on Widows.   From Finder, it's locked; I cannot access it.  Could I use the command line and change ownership or access privledges?  And then get acces

Re: Fw: Re: Could Not Connect To Server

On 4/23/20 4:19 PM, Dummy Account wrote: This worked. sudo su - postgres I was now able to log into data and ls Inside data is "log". How do I open log? and got: LSOpenURLsWithRole() failed with erro -610 I then tried: log show and I got: log: Could not be open local log store: The log archive

Re: Fw: Re: Re: Could Not Connect To Server

On 4/23/20 4:46 PM, Dummy Account wrote: I cd'd into log and listed the files.  There are many. How should I get the log that you want. I tried: open -a TextEdit postgresql-2020-04-22_171300.log I got: LSOpenURLsWithRole() failed for the application /Applications/TextEdit.app with error -61

Re: psql \copy

On 4/24/20 10:12 AM, Steve Clark wrote: On 04/24/2020 11:59 AM, Steve Crawford wrote: On Fri, Apr 24, 2020 at 8:55 AM Steve Clark mailto:steve.cl...@netwolves.com>> wrote: Hello, I am using psql to copy data extracted from an InfluxDB in csv format into postgresql. I have a

Re: Thoughts on how to avoid a massive integer update.

On 5/4/20 3:32 PM, Fehrle, Brian wrote: Hi all, This is a shot in the dark in hopes to find a magic bullet to fix an issue I have, I can’t personally think of any solution myself. I have a database with hundreds of terabytes of data, where every table has an integer column referencing a s

Re: Thoughts on how to avoid a massive integer update.

On 5/4/20 3:32 PM, Fehrle, Brian wrote: Hi all, This is a shot in the dark in hopes to find a magic bullet to fix an issue I have, I can’t personally think of any solution myself. I have a database with hundreds of terabytes of data, where every table has an integer column referencing a s

Re: pg_dump negation regex

> On May 7, 2020, at 1:03 PM, Eudald Valcàrcel Lacasa > wrote: > > I know the -T command, but I'm trying to regex the "pattern" in -T in order > to exclude all tables named program_1 onwards. > I believe commonly you'd use -T "program_!0", but the ! negation keyword is > not defined. > > T

Re: Thoughts on how to avoid a massive integer update.

>Could you show an example table relationship? > > It’s a simple one-to-many relationship: > *Info_table* > info_table_sid integer > > > *data_table* > data_table_sid integer, > info_table_id integer references info_table(info_table_sid), > > > Right, and now you wish to change the value

Re: Thoughts on how to avoid a massive integer update.

id and “info_table_sid” as an editable attribute Nothing is broken other than the per-table lock while you switch the foreign key > On May 8, 2020, at 1:36 PM, Fehrle, Brian wrote: > > > > From: Rob Sargent mailto:robjsarg...@gmail.com>> > Date: Friday, May

Re: Thoughts on how to avoid a massive integer update.

> On May 8, 2020, at 2:37 PM, David G. Johnston > wrote: > > On Fri, May 8, 2020 at 12:49 PM Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: > Well as I said, I think you could add a column to info_table > alter table info_table add orig_id int; >

Re: Thoughts on how to avoid a massive integer update.

> On May 8, 2020, at 2:43 PM, David G. Johnston > wrote: > > On Fri, May 8, 2020 at 1:41 PM Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: > My understanding is the keys in the info_table need to change. That causes > the very expensive update in the up

Re: Thoughts on how to avoid a massive integer update.

> On May 8, 2020, at 2:57 PM, David G. Johnston > wrote: > > On Fri, May 8, 2020 at 1:51 PM Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: > >> On May 8, 2020, at 2:43 PM, David G. Johnston > <mailto:david.g.johns...@gmail.com>> wrote: >>

Re: Thoughts on how to avoid a massive integer update.

ge. Oh heck do the switcheroo and mail out a magic decoder ring ;) > > From: Rob Sargent mailto:robjsarg...@gmail.com>> > Date: Friday, May 8, 2020 at 3:05 PM > To: "David G. Johnston" <mailto:david.g.johns...@gmail.com>> > Cc: "Fehrle, Brian&quo

schema agnostic functions in language sql

I'm using postgres 12.2, with multiple identical schema per database (each with a matching role).  I can write public plpgsql functions without using a schema identifier and let the runtime search_path find the correct schema-dependent table.  The same does not appear to be true for plain sql f

Re: schema agnostic functions in language sql

On 5/15/20 4:43 PM, Adrian Klaver wrote: On 5/15/20 3:26 PM, Rob Sargent wrote: I'm using postgres 12.2, with multiple identical schema per database (each with a matching role).  I can write public plpgsql functions without using a schema identifier and let the runtime search_path fin

Re: schema agnostic functions in language sql

On 5/15/20 4:58 PM, Adrian Klaver wrote: On 5/15/20 3:53 PM, Rob Sargent wrote: On 5/15/20 4:43 PM, Adrian Klaver wrote: On 5/15/20 3:26 PM, Rob Sargent wrote: I'm using postgres 12.2, with multiple identical schema per database (each with a matching role).  I can write public pl

Re: schema agnostic functions in language sql

On 5/15/20 5:16 PM, David G. Johnston wrote: On Fri, May 15, 2020 at 4:07 PM Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: I'm terribly sorry:  I needed to add that plpgsql works without any knowledge of the schema, where as defining a plain sql functions doe

Re: schema agnostic functions in language sql

On 5/15/20 5:41 PM, Tom Lane wrote: "David G. Johnston" writes: Yes, SQL and pl/pgsql have very different behaviors when it comes to compilation and execution. In particular SQL performs parsing earlier (during creation - just like it does for views) and links the textual query to its parse

Re: schema agnostic functions in language sql

> On >>> regards, tom lane >> Did my message with a sql and plgpsql versions not come through? >> I cannot create a plain sql function unless the search_path covers any table >> mentioned. Not the case when using plpgsql - no path needed. > > But does the plpgsql segment_calls() ru

Re: schema agnostic functions in language sql

> On May 15, 2020, at 6:03 PM, Tom Lane wrote: > > Rob Sargent writes: >> I cannot create a plain sql function unless the search_path covers any >> table mentioned. Not the case when using plpgsql - no path needed. > > Oh, one of the things that's quite

Re: Inherited an 18TB DB & need to backup

> O >> Another problem is storage devices fail. S3 storage lakes _should_ be >> checking your data integrity on a regular basis and possibly maintaining >> copies of it iin multiple locations so you're not vulnerable to a site >> disaster. > > Tape FTW!! > > -- Or WTF Tape?? :) > Angula

Re: schema agnostic functions in language sql

> On May 16, 2020, at 9:13 AM, David G. Johnston > wrote: > >  >> On Sat, May 16, 2020 at 5:15 AM Rob Sargent wrote: >> check_function_body=off may be what I want during the site install as the >> definitions should be correct in all aspects. > > You

Re: SET ROLE and search_path

On 5/20/20 10:36 AM, Patrick FICHE wrote: Hi, I’m trying to implement a PostgreSQL multi-tenant database that will be accessed by a Web Application. The users that will login will belong to different companies and a schema was created in the database for each company. However, I would l

Re: SET ROLE and search_path

On 5/20/20 1:28 PM, Adam Brusselback wrote: I have this exact setup, and I use roles / schema names that match so the $user var works with the search path when I set role as my application user. > When search_path contains “$user”, does it refer to session_user or current_user ? It uses cur

Re: Can we get SQL Server-like cross database queries

> On Jun 3, 2020, at 1:46 PM, Tom Lane wrote: > > Guyren Howe writes: >> Is it practical to provide the SQL Server-like feature in Postgres? > > No. > > regards, tom lane > > That got me chuckling. I had just decided not to bother posting, but well, here goes. I cal

Re: Multitenent architecture

> On Jun 5, 2020, at 2:54 AM, Vasu Madhineni wrote: > >  > If the data size is more than 6TB, which approach better? > >> On Fri, Jun 5, 2020 at 2:57 PM Laurenz Albe wrote: >> On Thu, 2020-06-04 at 23:52 +0800, Vasu Madhineni wrote: >> > We are planning a POC on multitenant architecture in P

Re: Multitenent architecture

> On Jun 5, 2020, at 9:57 PM, Vasu Madhineni wrote: > >  > Hi Rob, > > Our environment is medical clinical data, so each clinic as a tenant. > Approximately 500+ tenants with 6TB data. > . How quickly are both those numbers growing? What are your cross clinic query requirements ? Any sin

Re: create batch script to import into postgres tables

> On Jun 19, 2020, at 1:26 PM, Pepe TD Vo wrote: > > thank you for all the information but I have no problem connecting to the > database using pgAdmin and/or directly psql from the postgres database. > > I need to set up a batch/cron job to run in Linux/AWS to ingest the data. > Therefore

Re: ERROR: invalid memory alloc request size 18446744073709551613

> On Jun 22, 2020, at 8:13 AM, Flaris Feller wrote: > > Hello all, > > When using Postgres 9.6.15 on "CentOS Linux release 7.5.1804 (Core)" on Intel > x86_64 I noticed "invalid memory alloc request size" error at PostgreSQL logs. > This is the postgresq.log file's fragment of log where the er

Re: PG Admin 4

On 7/10/20 9:53 AM, rwest wrote: I'm relatively new to PostgreSql and am trying to navigate my way around the tools like PG Admin 4 to do Database Admin work. I'm trying to run an entire set of DDL with lots of tables, indexes, etc. through PG Admin 4 for a database. The only thing I saw aft

<    1   2   3   4   5   6   7   >