Re: SQL syntax

[Rob Sargent]

> On Mar 2, 2018, at 5:41 PM, Adrian Klaver  wrote:
> 
>> On 03/02/2018 04:36 PM, Dale Seaburg wrote:
>> My mind is drawing a blank.  Not sure where to go to find the answer. Here 
>> is the statement in C#:
>> sSQL = "SELECT \"Image_Filename\" FROM \"Instruments\" WHERE ";
>> To finish off the WHERE clause, I need to look at the first 2 letters, like 
>> "D:".  My question is how do I specify in the WHERE clause, to look at the 
>> first 2 characters in the Image_Filename column?  What is the correct SQL 
>> syntax for looking at just a portion of a column?
> 
> SELECT "Image_Filename" FROM "Instruments" WHERE "ImageFilename" LIKE 'D:%';
> 
> If you want case insensitive then ILIKE. For more info:
> https://www.postgresql.org/docs/10/static/functions-matching.html#FUNCTIONS-LIKE
> 
>> I feel so dumb...  This can't be that hard, but I am exhausted and running 
>> out of ideas.  I need someone to give me a boost!  ;-)
>> Thanks,
>> Dale.
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com
> 
Use the tilda, Luke. 
filename ~ ‘^D:’

Re: postgresql-10.3 on unbuntu-17.10 - how??

[Rob Sargent]

Thanks, I forgot that the older repos also received the pg-10 update.
Unfortunately but no luck with Xenial either, slightly different
but similar conflicts.

My main motivation for updating to 10.3 was to be able to load data
dumped from a 10.3 database.  pg_restore complained about "unsupported
version (1.13) in file header".  However I just discovered I can load
a plain sql dump from that database so panic mode is over :-) and I
can wait until I can upgrade my machine to ubuntu-18.04.

It still seems to me that the best advice for using Postgresql on
Ubuntu is to use the Ubuntu version of Postgresql if you don't need
the latest version; if you do need latest version, use the Pgdg
version but only with a LTS version of Ubuntu.

If you need the latest version of both Ubuntu and Postgresql, you
may be out of luck.



Or you compile it?

Re: postgresql-10.3 on unbuntu-17.10 - how??

[Rob Sargent]

Or you compile it?


That was going to be my next step.  But I don't think a simple compile
from source would do because Ubuntu's package manager wouldn't be aware
that Postgresql was now available to satisfy other packages' 
dependencies.

So I would need to rebuild the Ubuntu source package.  I have done that
on Fedora several times where it has been, in my limited experience,
usually simple and problem free.  But I have read that building packages
on Ubuntu is much more arcane so I wasn't looking forward to it.


That is pretty much the case when you build from source, it will live 
outside the OS packaging universe. I have built from source on Ubuntu 
it is not any more difficult then other distros, just remember to 
install build-essential. As far a dependencies basically the only 
things that will have a Postgres dependency will be other Postgres 
software e.g. psycopg2, etc. That means you will need to build them 
from source also, though that is helped along by pg_config which will 
find your source install and build the other software to match. It 
also means uninstalling the Ubuntu Postgres packages so you don't 
cross contaminate.



And I would have expected to need to get a current library or two and 
thence a custom load path.  That said, it might not have taken as long 
as the packaged based efforts.  Nothing near as quick as realizing one 
can just load the sql though!

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

[Rob Sargent]

On 03/28/2018 08:29 PM, Ken Beck wrote:

Is it possible a big problem is that the password for user postgres not
longer works for some reason, not sure why. It appears the password can
not be rest or changed without knowing the original, and what I thought
was the original no longer works.



Maybe the install nulled (reset) the password but you can change it:

sudo su -
passwd postgres

Re: Please suggest the best suited unit test frame work for postgresql database.

[Rob Sargent]

On 04/02/2018 10:58 AM, Steven Lembark wrote:

On Sun, 1 Apr 2018 10:26:32 +0530
Raghavendra Rao J S V  wrote:


Good morning.

Please suggest the best suited unit test frame work for postgresql
database and also shared the related documents to understand the
framework.

If anyone there knows Perl (if not I'll be happy to teach it)

That's just being mean ;)

Re: single sql, multiple schemas, one result set

[Rob Sargent]

On 04/03/2018 11:47 AM, PegoraroF10 wrote:

Suppose a DB with dozens of schemas with same structure.
DB
   Schema1
 Table1
 Table2
   Schema2
 Table1
 Table2
   Schema3
 Table1
 Table2
Then we want to execute a SQL on specific schemas and the result of it could
be a UNION ALL. So, how could be a function that runs that SQL on each
schema and results just on result set ?

Then I would use something like ...
select * from SQLFromMultipleShema(Array['Schema1,Schema3'],'select count(*)
from Table2');




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


Should work if you add a tag to every table referenced in the sql sent in:

select * from SQLFromMultipleShema(Array['Schema1,Schema3'],'select count(*)
from SCHEMA_NAME.Table2')

Then, in a loop over the schema name array replacing the SCHEMA_NAME as you 
build up a union of your injected sql.

Or perhaps loop over the schame name array reseting search_path as you build up 
a temp table of the result from the sql passed in as an argument.

Pretty sure you're going to end up need "dynamic sql";

Re: Archiving Data to Another DB?

[Rob Sargent]

On 04/11/2018 10:24 AM, Ron wrote:



On 04/11/2018 11:15 AM, Don Seiler wrote:

Let's say I have two DBs: main (9.6.6) and archive (10.2).

I have a table in main where I want to archive data older then 60 
days. For various reasons, the table is not partitioned, so for now 
we must use DELETE. The destination table in the archive DB is 
partitioned with the new Pg10 partitioning.


My initial plan was to have a stored procedure on the archive DB use 
postgres_fdw to do an INSERT INTO / SELECT to select the data 
remotely and insert into the local archive table. It would then issue 
a single DELETE command to remotely remove the data from the main DB. 
However I found that doing this resulted in the main DB calling 
thousands (perhaps millions if it's one-per-row) of individual DELETE 
statements based on a ctid column. Aside from WAL behavior concerns, 
it is flooding my postgresql server logs since I log any DML.


On top of that, I'm told that a remote DELETE wouldn't be 
transactional, so if I were to compare inserted rows vs deleted rows 
and found a mismatch, I couldn't just rollback the DELETE. I plan to 
verify this with a small test case later but for now I'll assume this 
to be true.


Right now I'm thinking of falling back to the far-less-elegant method 
of dumping the data to a flat file via COPY, running psql to connect 
to the archive DB remotely and running a COPY to load the data (or 
maybe transferring the flat file to the archive DB to load it there, 
offloading that part of the workload), then deleting the data from 
the main DB. I could capture the rows dumped in a control table and 
compare the rows deleted against that and then rollback the delete if 
necessary.


Like I said, not elegant, but I don't want to risk losing data that 
wasn't successfully archived to the archive DB. I'm very interested 
to hear what others might be doing for tasks like this.


It might not be elegant, but a COPY / DELETE / LOAD is granular, so 
you can restart at any point.




I might be inclined to COPY/LOAD/check/DELETE

Re: hardcode password in connect string

[Rob Sargent]

On 04/13/2018 12:46 PM, James Keener wrote:
Is setting it as an environment variable an option? 
https://www.postgresql.org/docs/9.1/static/libpq-envars.html


Alternatively, a service file? 
https://www.postgresql.org/docs/9.0/static/libpq-pgservice.html


Jim

On April 13, 2018 2:43:01 PM EDT, David Gauthier 
 wrote:


Hi:

PG v9.5.2 on RHEL

I like to use an alias to connect to my favorite DBs but don't
like to enter passwords.  I used to just disable passwords
(working behind a firewall), but this one is different.  I see
nothing in the interactive connect string where I can enter the
password...

psql -h thehost -U memyselfi mydb

Missing something like "-p mypassword"

Is there a way I can stick the pw in the linux alias definition ?

Just thinking something like this may be available since hardcoded
passwords are supported in perl/dbi, pg driver

Thanks !


--
Sent from my Android device with K-9 Mail. Please excuse my brevity. 

This is usually done in ~/.pgpass

Re: Postgresql database encryption

[Rob Sargent]

On 04/20/2018 05:43 PM, Ron wrote:



On 04/20/2018 06:11 PM, Stephen Frost wrote:

Greetings,

* Ron (ronljohnso...@gmail.com) wrote:

On 04/20/2018 03:55 PM, Vick Khera wrote:

On Fri, Apr 20, 2018 at 11:24 AM, Vikas Sharma For anyone to offer a proper solution, you need to say what purpose 
your
encryption will serve. Does the data need to be encrypted at rest? 
Does it

need to be encrypted in memory? Does it need to be encrypted at the
database level or at the application level? Do you need to be able to
query the data? There are all sorts of scenarios and use cases, and 
you

need to be more specific.

For me, using whole-disk encryption solved my need, which was to 
ensure

that the data on disk cannot be read once removed from the server.
Someone really needs to explain that to me. My company-issued laptop 
has
WDE, and that's great for when the machine is shut down and I'm 
carrying it
from place to place, but when it's running, all the data is 
transparently
decrypted for every process that wants to read the data, including 
malware,

industrial spies,

Thus, unless you move your DB server on a regular basis, I can't see 
the

usefulness of WDE on a static machine.

The typical concern (aka, attack vector) isn't around moving the DB
server on a regular basis or about someone breaking into your data
center and stealing your drives, it's making sure that disposal of
equipment doesn't result in valuable data being retained on the
drives when they leave the data center for replacement or disposal.


That makes some sense, but years of added CPU overhead to mitigate a 
problem that could be solved by writing zeros to the disk as a step in 
the decomm process seems more than a bit wasteful.



Well you probably need to drive a nail through the drive but that's a 
technical detail :)

Surprised by index choice for count(*)

[Rob Sargent]

Should I be?  I would have thought the pk would have been chosen v. 
function index?


explain analyse select count(*) from bc.segment s;
QUERY PLAN
---
 Finalize Aggregate  (cost=4428009.24..4428009.25 rows=1 width=8) 
(actual time=14786.395..14786.395 rows=1 loops=1)
   ->  Gather  (cost=4428009.03..4428009.24 rows=2 width=8) (actual 
time=14786.358..14786.386 rows=3 loops=1)

 Workers Planned: 2
 Workers Launched: 2
 ->  Partial Aggregate  (cost=4427009.03..4427009.04 rows=1 
width=8) (actual time=14782.167..14782.167 rows=1 loops=3)
   ->  Parallel Index Only Scan using fpv on segment s  
(cost=0.57..4210177.14 rows=86732753 width=0) (actual 
time=0.061..11352.855 rows=69386204 loops=3)

 Heap Fetches: 1780
 Planning time: 0.221 ms
 Execution time: 14815.939 ms
(9 rows)

\d bc.segment
  Table "bc.segment"
 Column |  Type   | Modifiers
+-+
 id | uuid    | not null
 chrom  | integer | not null
 markerset_id   | uuid    | not null
 probandset_id  | uuid    | not null
 startbase  | integer | not null
 endbase    | integer | not null
 firstmarker    | integer | not null
 lastmarker | integer | not null
 events_less    | bigint  | not null default 0
 events_equal   | bigint  | not null default 0
 events_greater | bigint  | not null default 0
Indexes:
    "segment_pkey" PRIMARY KEY, btree (id)
    "useg" UNIQUE, btree (probandset_id, chrom, startbase, endbase)
    "fpv" btree (pv(events_less, events_equal, events_greater, 0))
    "segment_markerset_id_probandset_id_idx" btree (markerset_id, 
probandset_id)


create or replace function public.pv(l bigint, e bigint, g bigint, o int)
returns float
as
$$
select 1.0*(g+e+o)/(l+e+g+o)::float;
$$
language sql
;

Re: Surprised by index choice for count(*)

[Rob Sargent]

Thank you both.  Simple, as expected.  And I’m easily surprised.  
Version 10 (perhaps obviously) for those scoring at home.

> On May 1, 2018, at 10:11 AM, Tom Lane  wrote:
> 
> Rob Sargent  writes:
>> Should I be?  I would have thought the pk would have been chosen v. 
>> function index?
> 
> If I'm reading this correctly, the PK index contains uuids while
> the fpv index contains float4s, meaning the latter is probably half
> the size.  So scanning it is a lot cheaper, at least according to
> the planner's cost model.
> 
>   regards, tom lane

Re: Add schema to the query

[Rob Sargent]

> On May 6, 2018, at 8:19 PM, Igor Korot  wrote:
> 
> Hi, ALL,
> 
> Is there an easy way to add the schema to the following query:
> 
> SELECT u.usename FROM pg_class c, pg_user u WHERE u.usesysid =
> c.relowner AND relname = ?
> 
> Thank you.
> 
Are you looking for the owner of a particular schema.tablename?

Re: Help with SQL

[Rob Sargent]

> On May 27, 2018, at 6:42 PM, anand086  wrote:
> 
> Hi,
> 
> I have a requirement of writing plpgsql function to create partial indexes
> on child tables if it exists on parent table. The function will have
> schemname, childtablename, tableowner as input.
> 
> I am using the below code to identify the indexname and index definition
> from parent table --
> 
> ```
> with idx as 
>(select indexrelid::regclass indexname, indisunique,
> indisprimary from pg_index where indrelid in 
>(select oid from pg_class where relname in (select
> tablename from pg_indexes where tablename='test_booking')
>) 
>   and indpred is not null
>) 
>select idxs.tablename, idxs.indexname, idxs.indexdef,
> idx.indisunique from pg_indexes as idxs join idx on
> idxs.indexname=split_part(idx.indexname::text , '.' ,2)
> ```
> 
> Suppose the indexdef is on parent table is --
> 
> CREATE UNIQUE INDEX uniq_test_booking_1 ON demo.test_booking USING btree
> (col1,col2 ,col3, col4,col5, col6) WHERE ((col4 IS NOT NULL) AND (col6 IS
> NOT NULL))
> 
> Now, what I am trying to achieve is to create and execute the below sql,
> wherein I replace the  indexname with uniq__
> and tablename with the childtablename part of function input.
> 
> CREATE UNIQUE INDEX uniq_test_booking_20180527_gdhsd ON
> demo.test_booking_20180527 USING btree (col1,col2 ,col3, col4,col5, col6)
> WHERE ((col4 IS NOT NULL) AND (col6 IS NOT NULL))
> 
> Using substring I am trying to break the SQL statement in 2 and then later
> concatenate it.
> The first part is substring(idxrec.indexdef from 0 for 21);  --> output is
> "create unique index " statement.
> and for the 2nd part, starting for USING until the end. But I am unable to
> get the 2nd part of sql.
> 
> 
> ```
>if idxrec.indisunique='t' then
>   SELECT substr(concat(md5(random()::text), md5(random()::text)), 
> 0,
> 7) into var;
>   idxname:='uniq_'||idxrec.tablename||'_'||var;
>   raise notice 'Index name will be %', idxname;
>   createStmts1:=substring(idxrec.indexdef from 0 for 21);  --> 
> gives me
> the "create unique index"
>   raise notice 'String1 %', createStmts1;
>   createStmts2:=
>   raise notice 'String2 %', createStmts2;
> ```
> 
> Is this the correct way? Any better suggestion? 
> 
> How can I achieve this?
> 
> 
Is using a stored procedure a required?  To do so would require “dynamic sql” 
but imho that’s not the best use of stored procedures.  Perhaps you could use 
your “with ids” sql to get the list of required indices with names, columns 
etc, place those values (sed, awk, perl, python, even sql) into versions of 
your create index code and stick those generated files in hopefully the same 
source code repository which has the definition of the parent and children 
tables.  They will likely all need maintenace at the same time.

Re: reduce number of multiple values to be inserted

[Rob Sargent]

> On May 29, 2018, at 6:10 PM, tango ward  wrote:
> 
> 
> Hi,
> 
> I am working on inserting multiple values for a table. I need to insert 3 
> values of data for each age of the students from the same village. It will be 
> 3 different ages of student per village.
> 
> My sample code:
> 
> 
> curr_pgsql.execute('''
>INSERT INTO student (created, modified, name,
> address, age, level
> )
>VALUES (current_timezone, current_timezone,
>'Scott', 'Malayan Village', 21, 2),
>   (current_timezone, current_timezone,
>'Ben', 'Malayan Village', 21, 2),
>   (current_timezone, current_timezone,
>'Scott', 'Malayan Village', 21, 2),
>  
>   (current_timezone, current_timezone,
>'Andrew', 'Malayan Village', 25, 2),
>   (current_timezone, current_timezone,
>'Larry', 'Malayan Village', 25, 2),
>   (current_timezone, current_timezone,
>'Adam', 'Malayan Village', 25, 2),
>   
>   (current_timezone, current_timezone,
>'Elisse', 'Malayan Village', 27, 2),
>   (current_timezone, current_timezone,
>'Xena', 'Malayan Village', 27, 2),
>   (current_timezone, current_timezone,
>'Karen', 'Malayan Village', 27, 2)
> 
> I will repeat the same process for 13 villages so that will be 117 of values. 
> I would like to know if there's a way to reduce the script? This has to be 
> done strictly via script.
> 
> 
> Thanks,
> J
> 
Is “current_timezone, current_timezone” just a typo?  I think you need to make 
the 117 data lines and load using \copy
 

Re: reduce number of multiple values to be inserted

[Rob Sargent]

> On May 29, 2018, at 6:32 PM, tango ward  wrote:
> 
> On Wed, May 30, 2018 at 8:29 AM, Rob Sargent  <mailto:robjsarg...@gmail.com>> wrote:
> 
> Is “current_timezone, current_timezone” just a typo?  I think you need to 
> make the 117 data lines and load using \copy
> 
> 
> 
> Sorry, yeah, it's current_timestamp.  
> 

David and I are suggesting the same thing.  You have 117 unique student 
definitions so you need to explicitly define each of them. That can be in your 
python code or in a file your python code reads in and generates inserts or 
simply a psql script which reads the file using \copy tablename from file.  

Re: reduce number of multiple values to be inserted

[Rob Sargent]

On 05/29/2018 06:36 PM, Adrian Klaver wrote:

On 05/29/2018 05:10 PM, tango ward wrote:


Hi,
Not sure where you are pulling the data from and how it is ordered, 
but an outline:


data_input (Assuming sorted by village and then age)

for village in data_input:
for age in village:
 curr_pgsql.execute('''
 INSERT INTO student (created, modified, name,
  address, age, level)
 VALUES(current_timezone, current_timezone,
    %s, %s, %s, 2)''', (name, village, age))




Thanks,
J





You might need random(name)? ;)

Re: Insert UUID GEN 4 Value

[Rob Sargent]

> On May 30, 2018, at 9:57 PM, tango ward  wrote:
> 
>> On Thu, May 31, 2018 at 11:53 AM, tango ward  wrote:
>>> On Thu, May 31, 2018 at 11:51 AM, Laurenz Albe  
>>> wrote:
>>> tango ward wrote:
>>> > I found this: 
>>> > 
>>> > CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
>>> > SELECT uuid_generate_v4();
>>> > 
>>> > My problem is I have a table like this:
>>> > CREATE TABLE enrollmentinfo (
>>> > id integer NOT NULL,
>>> > created timestamp with time zone NOT NULL,
>>> > modified timestamp with time zone NOT NULL,
>>> > secure_id uuid NOT NULL,
>>> > relationship character varying(50) NOT NULL,
>>> > tuition_bill character varying(255) NOT NULL
>>> > );
>>> > 
>>> > Now I need to insert data into this table, I just don't know if I can use 
>>> > something like this
>>> > "INSERT INTO enrollmentinfo (current_timestamp, current_timestamp, 
>>> > uuid_generate_v4(), '', '');
>>> > I haven't tried this but also not sure if I can call the function inside 
>>> > INSERT.
>>> 
>>> Why didn't you try it?
>>> 
>>> I see no problem with that.
>>> 
>>> Yours,
>>> Laurenz Albe
>>> -- 
>>> Cybertec | https://www.cybertec-postgresql.com
>>> 
>> 
>> 
>> Okay I will try it.
> 
> When I tried it, I am getting an error: Invalid input syntax for UUID: 
> uuid_generate_v4(),
What do you get from
Select uuid_generate_v4();

Re: Query hitting empty tables taking 48 minutes

[Rob Sargent]

On 06/07/2018 12:55 PM, Robert Creager wrote:



On Jun 7, 2018, at 12:40 PM, Adrian Klaver > wrote:


On 06/07/2018 11:17 AM, Robert Creager wrote:
I have a system running FreeBSD 11.1-STABLE, PostgreSQL 9.6.8,Java 
OpenJDK 1.8.0_131, jdbc 9.3-1104-jdbc41 which is exhibiting very 
bizarre behavior.  A query is executing against a couple of tables 
that have 1 and 0 records in them.  ds3.job_entry has 0 rows, 
ds3.blob has 1 row.   If I execute the query manually via command 
line, it executes fine.   There are no other active queries, no 
locks.  The system is idle, in our Dev Test group, so I can leave it 
this way for a bit of time.  The general software setup is in 
production and I’ve seen nothing like this before.  Even a system 
with 300M ds3.blob entries executes this query fine.


So I am assuming the problem query is being run through Java/jdbc, 
correct?


Yes.



There is also the below in the log:

" ... execute fetch from S_2037436/C_2037437 …"


So, that means nothing to me.  Something to you?



My guess is that we will need to see the Java code that sets up and 
runs the query. Is that possible?


OMG, you had to ask ;-)  Not really, for two reasons.  It’s an actual 
shipping product, and I’d have to send you so much code to get from 
the source of the query down through the execute…. Now, the execute, 
on it’s own, is below.  m_statement is a java.sql.PreparedStatement. 
 Keep in mind this code is literally executing millions of times a day 
on a busy system.  Except for this special snowflake system...


private Executor executeQuery()
{
    final MonitoredWork monitoredWork = new MonitoredWork(
StackTraceLogging.NONE, m_readableSql.getLogMessagePreExecution() );
    try
    {
        m_closeTransactionUponClose = ( null == m_transactionNumber );
        m_statement.setFetchSize( 1 );
        final Duration duration = new Duration();
        m_resultSet = m_statement.executeQuery();
        m_readableSql.log( duration, null );
        return this;
    }
    catch ( final SQLException ex )
    {
        throw new DaoException(
                "Failed to execute: 
" + m_readableSql.getLogMessagePreExecution(),

                ex );
    }
    finally
    {
        monitoredWork.completed();
    }
}



Jun  7 17:24:21 blackpearl postgres[10670]: [7737-1] 
db=tapesystem,user=Administrator,app=[unknown],client=127.0.0.1 
LOG:  duration: *2903612.206* ms  execute fetch 
from S_2037436/C_2037437: SELECT * FROM ds3.blob WHERE EXISTS 
(SELECT * FROM ds3.job_entry WHERE blob_id = ds3.blob.id 
  AND (job_id = $1))
tapesystem=# explain analyze SELECT * FROM ds3.blob WHERE EXISTS 
(SELECT * FROM ds3.job_entry WHERE blob_id = ds3.blob.id 
  AND (job_id = 
'b51357cd-e07a-4c87-a50b-999c347a5c71'));

      QUERY PLAN
--- 
  Nested Loop  (cost=0.84..6.89 rows=1 width=77) (actual 
time=0.044..0.044 rows=0 loops=1)
   ->  Index Scan using job_entry_job_id_idx on 
job_entry  (cost=0.42..2.44 rows=1 width=16) (actual 
time=0.042..0.042 rows=0 loops=1)
         Index Cond: (job_id = 
'b51357cd-e07a-4c87-a50b-999c347a5c71'::uuid)
   ->  Index Scan using blob_pkey on blob  (cost=0.42..4.44 rows=1 
width=77) (never executed)

         Index Cond: (id = job_entry.blob_id)
 Planning time: 0.388 ms
 Execution time: 0.118 ms
pidclient_portruntimequery_startdatnamestatequeryusename
10670112110 years 0 mons 0 days 0 hours 43 mins 28.852273 secs 
2018-06-07 17:24:22.026384tapesystemactiveSELECT * FROM ds3.blob 
WHERE EXISTS (SELECT * FROM ds3.job_entry WHERE blob_id = 
ds3.blob.id   AND (job_id = 
$1))Administrator
From the system with 300M ds3.blob entries, which works fine (along 
with every other system in house):

QUERY PLAN
Nested Loop  (cost=0.57..738052.90 rows=164379 width=75) (actual 
time=1.001..1947.029 rows=5 loops=1)
  ->  Seq Scan on job_entry  (cost=0.00..10039.50 rows=164379 
width=16) (actual time=0.871..56.442 rows=5 loops=1)

        Filter: (job_id = 'ef27d2fa-2727-424e-8f44-da9e33a5ca05'::uuid)
        Rows Removed by Filter: 60001
  ->  Index Scan using blob_pkey on blob  (cost=0.57..4.42 rows=1 
width=75) (actual time=0.037..0.037 rows=1 loops=5)

        Index Cond: (id = job_entry.blob_id)
Planning time: 6.330 ms
Execution time: 1951.564 ms
Please keep my CC of my work e-mail present.
Best,
Robert



--
Adrian Klaver
adrian.kla...@aklaver.com 



What's the url doing in "blob_id = ds3.blob.id 

Re: Query hitting empty tables taking 48 minutes

[Rob Sargent]

On 06/07/2018 01:13 PM, Adrian Klaver wrote:

On 06/07/2018 12:11 PM, Rob Sargent wrote:







What's the url doing in "blob_id = ds3.blob.id <http://ds3.blob.id";?


I have run into this before, it is an email artifact.


OK, thanks.
The code presented is just a generalized sql executor.  Might need to 
see the code generating the sql.  A complete definition of the stack 
might be in order too.  Might explain the "fetch" remark.

Re: Run Stored procedure - function from VBA

[Rob Sargent]

> On Jun 18, 2018, at 9:47 AM, Łukasz Jarych  wrote:
> 
> CREATE OR REPLACE FUNCTION totalRecords ()
> RETURNS integer AS $total$
> declare
>   total integer;
> BEGIN
>SELECT count(*) into total FROM COMPANY;
>RETURN total;
> END;
> $total$ LANGUAGE plpgsql;

Re: Run Stored procedure - function from VBA

[Rob Sargent]

> On Jun 18, 2018, at 9:47 AM, Łukasz Jarych  wrote:
> 
> Hi Guys,
> 
> i have example function :
> 
> CREATE OR REPLACE FUNCTION totalRecords ()
> RETURNS integer AS $total$
> declare
>   total integer;
> BEGIN
>SELECT count(*) into total FROM COMPANY;
>RETURN total;
> END;
> $total$ LANGUAGE plpgsql;
> 
> and i want to run it from VBA using odbc connection. 
> 
> What is the best way to use it ?
> 
> something like this: 
> 
> Dim dbCon as new ADODB.Connection
> Dim rst as new ADODB.Recordset
> 
> Dbcon.connectionstring=”Your connection string goes here!”
> Dbcon.open
> 
> Rst.open strsql
> where strsql is "Select * from totalRecords" or this is not a good solution?
> 
> Best,
> Jacek

You need the parentheses after the function name: “select * from 
totalrecords();"

Re: Is postorder tree traversal possible with recursive CTE's?

[Rob Sargent]

On 06/19/2018 01:14 PM, Hellmuth Vargas wrote:


Hi

with partial sum:




with recursive pizza (name, step, ingredient, quantity, unit, rel_qty, 
path, weight)

as (
        select
                name, step, ingredient, quantity, unit
        ,       quantity::numeric(10,2)
        ,       step::text
        ,       case when unit = 'g' then quantity::numeric(10,2) else 
null end

          from recipe
         where name = 'pizza'
        union all
        select
recipe.name , recipe.step, recipe.ingredient, 
recipe.quantity, recipe.unit

        ,       (pizza.rel_qty * recipe.quantity)::numeric(10,2)
        ,       pizza.path || '.' || recipe.step
        ,       case when recipe.unit = 'g' then (pizza.rel_qty * 
recipe.quantity)::numeric(10,2) else null end

          from pizza
          join recipe on (recipe.name  = 
pizza.ingredient)

)
select path, ingredient, quantity, rel_qty, unit, weight,*sum(weight) 
over(partition by split_part(path,'.',1)) as parcial_weight*, 
*sum(weight) over() as total_weight*

  from pizza
 order by path;

 path  |  ingredient  | quantity | rel_qty | unit  | weight | 
parcial_weight | total_weight

---+--+--+-+---+++--
 1     | tomato sauce |     1.00 |    1.00 | pcs   |        |        
 113.00 |      313.00
 1.1   | tomato  |   100.00 |  100.00 | g     | 100.00 |        
 113.00 |      313.00
 1.2   | basil |    10.00 |   10.00 | g     |  10.00 |         113.00 
|      313.00
 1.3   | salt  |     3.00 |    3.00 | g     |   3.00 |         113.00 
|      313.00
 2     | pizza bottom |     1.00 |    1.00 | pcs   |        |        
 200.00 |      313.00
 2.2   | dough |     1.00 |    1.00 | pcs   |        |         200.00 
|      313.00
 2.2.1 | flour |   150.00 |  150.00 | g     | 150.00 |         200.00 
|      313.00
 2.2.2 | water |    50.00 |   50.00 | g     |  50.00 |         200.00 
|      313.00
 2.2.3 | salt  |     1.00 |    1.00 | pinch |        |         200.00 
|      313.00

(9 rows)




This is gorgeous but I suspect any level greater than 10 wide will 
present sorting problems, no?  Maybe a fixed two-digit, zero filled 
number per level? Pushing the problem off by an order of magnitude :)

An exercise left to the OP perhaps.

Re: Run Stored procedure - function from VBA

[Rob Sargent]

On 06/18/2018 09:51 PM, Łukasz Jarych wrote:

Thank you Rob,

question is it is the optimal way to run SP from VBA?
Or not?

Best,
Jacek

2018-06-19 1:34 GMT+02:00 Rob Sargent <mailto:robjsarg...@gmail.com>>:





On Jun 18, 2018, at 9:47 AM, Łukasz Jarych mailto:jarys...@gmail.com>> wrote:

Hi Guys,

i have example function :

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

and i want to run it from VBA using odbc connection.

What is the best way to use it ?

something like this:

|Dim dbCon asnew ADODB.Connection Dim rst asnew ADODB.Recordset
Dbcon.connectionstring=”Your connection string goes
here!”Dbcon.openRst.openstrsql|
where strsql is "Select * from totalRecords" or this is not a
good solution?

Best,
Jacek


You need the parentheses after the function name: “select * from
totalrecords();"




Depends on the usage pattern.  I'm sure there is an ODBC construct for 
stored procedures/function, which you could build once and re-use with 
new parameter values if you're going to call this repeatedly.

Re: Load data from a csv file without using COPY

[Rob Sargent]

On 06/19/2018 03:14 PM, Ravi Krishna wrote:

If performance is relevant then your app should probably be using COPY 
protocol, not line by line inserts. It's
supported by most postgresql access libraries. If your app does that then using 
"\copy" from psql would be
an appropriate benchmark.

Actually the reluctance to not use COPY is to make the benchmark same across 
two different RDBMS in
two diff env.
If bulk loading is the actual production target, all your RDBMS choices 
have their own loaders.  I suggest that is what you ought tocompare.

Re: Run Stored procedure - function from VBA

[Rob Sargent]

Sorry. I don’t use ODBC directly. If it’s not obvious in the manual, google 
‘ODBC functions’

> On Jun 19, 2018, at 10:39 PM, Łukasz Jarych  wrote:
> 
> Thank you Rob,
> 
> exactly. Do you know this odbc constructtion?
> 
> Best,
> Jacek 
> 
> 2018-06-20 0:08 GMT+02:00 Asif Ali :
>> how the fuck i unsubscribe to this mailing list , i get more than 100 emails 
>> a day
>> 
>> Bye
>> 
>>  
>> From: Rob Sargent 
>> Sent: Wednesday, June 20, 2018 12:54 AM
>> To: Łukasz Jarych
>> Cc: pgsql-gene...@postgresql.org
>> Subject: Re: Run Stored procedure - function from VBA
>>  
>> 
>> 
>>> On 06/18/2018 09:51 PM, Łukasz Jarych wrote:
>>> Thank you Rob,
>>> 
>>> question is it is the optimal way to run SP from VBA? 
>>> Or not?
>>> 
>>> Best,
>>> Jacek
>>> 
>>> 2018-06-19 1:34 GMT+02:00 Rob Sargent :
>>> 
>>> 
>>>> On Jun 18, 2018, at 9:47 AM, Łukasz Jarych  wrote:
>>>> 
>>>> Hi Guys,
>>>> 
>>>> i have example function :
>>>> 
>>>> CREATE OR REPLACE FUNCTION totalRecords ()
>>>> RETURNS integer AS $total$
>>>> declare
>>>> total integer;
>>>> BEGIN
>>>>SELECT count(*) into total FROM COMPANY;
>>>>RETURN total;
>>>> END;
>>>> $total$ LANGUAGE plpgsql;
>>>> 
>>>> and i want to run it from VBA using odbc connection. 
>>>> 
>>>> What is the best way to use it ?
>>>> 
>>>> something like this: 
>>>> 
>>>> Dim dbCon as new ADODB.Connection
>>>> Dim rst as new ADODB.Recordset
>>>> 
>>>> Dbcon.connectionstring=”Your connection string goes here!”
>>>> Dbcon.open
>>>> 
>>>> Rst.open strsql
>>>> where strsql is "Select * from totalRecords" or this is not a good 
>>>> solution?
>>>> 
>>>> Best,
>>>> Jacek
>>> 
>>> You need the parentheses after the function name: “select * from 
>>> totalrecords();"
>>> 
>>> 
>> 
>> Depends on the usage pattern.  I'm sure there is an ODBC construct for 
>> stored procedures/function, which you could build once and re-use with new 
>> parameter values if you're going to call this repeatedly.
> 

Re: CTE optimization fence

[Rob Sargent]

I don’t think the fence analogy works. Try wall (a la Berlin). 
I count myself amongst those who thought “Ooh this little CTE will garner just 
what the rest of the query needs”. Only to find the planner didn’t groc that 
optimization. 

Is it a matter of deciding to trust the SQLer and runtime analyzing the CTE 
product before continuing? As an SQLer I have doubts about that precept but 
without _some_ change in attack CTEs approach fluff. 

> On Jun 26, 2018, at 11:45 PM, Thomas Kellerer  wrote:
> 
> Tom Lane schrieb am 27.06.2018 um 05:48:
>>> I see there was some discussion last year about removing the CTE 
>>> optimization fence (e.g. 
>>> http://www.postgresql-archive.org/CTE-inlining-td5958992.html) but can't 
>>> find anything more recent. Does anyone know if this is still under 
>>> consideration?
>> 
>> but we have to settle on a way of controlling it.
> 
> +1 from me. 
> 
> I am running more and more into situations where people consider this a bug 
> rather than a feature.
> 
> FWIW, I think a GUC that switches between the current (mostly unwanted, at 
> least surprising) 
> way and one where the CTE is optimized together with the main query would 
> suit "most" people.
> 
> For sake of compatibility this could default to the current behaviour
> 

Re: Not able to update some rows in a table

[Rob Sargent]

On 07/02/2018 09:59 AM, Marco Fochesato wrote:



I would suggest writing a self-contained script that creates the
table, inserts a single record, and updates that record.  Present
that for consideration along with a description or capture of the
results of running the script on your machine.


But not all the records fail the update...



We won't ge anywhere until you show the actual sql and the actual error(s)

Re: FK v.s unique indexes

[Rob Sargent]

> On Jul 5, 2018, at 1:30 AM, Rafal Pietrak  wrote:
> 
> 
> 
> W dniu 04.07.2018 o 00:55, David G. Johnston pisze:
>> On Tuesday, July 3, 2018, Rafal Pietrak > > wrote:
>> 
>> 
>>ERROR:  there is no unique constraint matching given keys for referenced
>>table "test2"
>>
>> 
>>I cannot see any reasons why this functionality is blocked.
>> 
>>In particular, contrary to what the ERROR says, the target table *does
>>have* a "unique constraint matching given keys", admittedly only
>>partial.
>> 
>> 
>> You are making the common error of confusing the distinct concepts of
>> constraints and indexs.  Table constraints cannot be partial by
>> definition, and are a logical concept constraining the data model. 
> 
> Hmmm..
> 
> This does not match "my reality". Naturally I may be wrong, but the
> example I've posted reflects my actual data I'm putting into the RDBMS.
> That is:
> 1. the data has unique constraint on (load,a,b,c)
> 2. and the data have additional unique constraints on (load,a), provided
> c is true, and (load,b) whenever c is false.
> 
> Pls consider in real life: load (a person), can have either a (a kind of
> brest cancer); or b (a kind of prostrate) - this is only a cooked
> example attemping to illustrate, that one may need to put additional
> constraints on the entire dataset.
> 
 
It’s difficult enough to define a unique person (without mother and father) and 
certainly this weeks definition of burden is not likely to help matters.  If 
you’re main worry is data consistency you might be better off normalizing your 
structure - either with separate tables per cancer type (person id, cancer 
specifics; unique on person) or in a single table one per line (person id, 
cancer type, cancer description; unique on person). You can reconstitue 
person,breast,prostate from either of those.  We won’t quibble on one person 
having both (though remotely possible, men do get breast cancer).

Re: Open Source tool to deploy/promote PostgreSQL DDL

[Rob Sargent]

On 07/10/2018 05:04 PM, Hustler DBA wrote:

Thanks Adrian and Rich,
I will propose sqitch to the client, but I think they want something 
with a GUI frontend.


They want to deploy database changes, track which environments the 
change was deployed to, be able to rollback a change (with a rollback 
script), track when and if the change was rolled back and in which 
environment/database... so pretty much a deployment and tracking GUI 
software with a frontend.


In the past, for doing database deployments to Oracle, I created a 
tool using PHP (frontend/backend), MySQL (repository to track 
deployments and store deployment logs) and scripted the release 
scripts for deployment and rollback, and had my tool manage the 
scripts up the environments. The client is "looking" for something 
more open source for PostgreSQL. Do we have anything similar to this?


Neil

open a github,gitlab,sorceforge repo, switch to postgres and make your 
opensource

Re: How to watch for schema changes

[Rob Sargent]

On 07/12/2018 05:28 PM, Igor Korot wrote:

Hi,


On Thu, Jul 12, 2018 at 12:16 PM, David G. Johnston
 wrote:

On Thu, Jul 12, 2018 at 8:50 AM, Igor Korot  wrote:

No, see:

https://www.postgresql.org/docs/10/static/sql-createtrigger.html

AFTER trigger on views are STATEMENT level only.

But I do have access to the STATEMENT right?


Yes, except nothing in the system actually attempts to directly target
information_schema views with updates so there will never be a triggering
event.

A normal trigger will not work - which is a large reason why event triggers
were implemented.

All of the alternative ideas (which I think was just log file parsing)
you've decided are not viable for your need.  Thus you've seemingly
eliminated all viable options and you now need to make a business decision.

[code]
MyMac:/ igorkorot$ find . -name postgresql.conf
find: ./.DocumentRevisions-V100: Permission denied
find: ./.fseventsd: Permission denied
find: ./.Spotlight-V100: Permission denied
find: ./.Trashes: Permission denied
find: ./dev/fd/3: Not a directory
find: ./dev/fd/4: Not a directory
find: ./Library/Application Support/Apple/ParentalControls/Users:
Permission denied
find: ./Library/Caches/com.apple.Spotlight/schema.501.plist: Permission denied
find: ./Library/Caches/com.apple.Spotlight/schema.502.plist: Permission denied
find: ./Library/Caches/com.apple.Spotlight/schema.89.plist: Permission denied
find: ./Library/Caches/com.apple.Spotlight: Permission denied
find: ./Library/PostgreSQL/9.1/data: Permission denied
find: ./private/etc/cups/certs: Permission denied
find: ./private/etc/raddb/certs: Permission denied
find: ./private/etc/raddb/modules: Permission denied
find: ./private/etc/raddb/sites-available: Permission denied
find: ./private/etc/raddb/sites-enabled: Permission denied
find: ./private/etc/raddb/sql: Permission denied
find: ./private/tmp/launchd-158.ac7XMn: Permission denied
find: ./private/tmp/launchd-47725.RroMYY: Permission denied
find: ./private/tmp/launchd-49727.qQpnIz: Permission denied
find: ./private/var/agentx: Permission denied
find: ./private/var/at/tabs: Permission denied
find: ./private/var/at/tmp: Permission denied
find: ./private/var/audit: Permission denied
find: ./private/var/backups: Permission denied
find: ./private/var/db/ConfigurationProfiles/Setup: Permission denied
find: ./private/var/db/dhcpclient: Permission denied
find: ./private/var/db/dslocal/nodes/Default: Permission denied
find: ./private/var/db/geod: Permission denied
find: ./private/var/db/krb5kdc: Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.0: Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.200:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.202:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.212:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.502:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.88:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.89:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.92:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.97:
Permission denied
find: ./private/var/db/locationd: Permission denied
find: ./private/var/db/Spotlight: Permission denied
find: ./private/var/db/sudo: Permission denied
find: ./private/var/folders/r9/_xhmtynj7xj22mwt_dcfbxqrgp/C:
Permission denied
find: ./private/var/folders/r9/_xhmtynj7xj22mwt_dcfbxqrgp/T:
Permission denied
find: 
./private/var/folders/zz/zyxvpxvq6csfxvn_n0/0/com.apple.revisiond.temp:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0/Cleanup
At Startup: Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0300r/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n08421/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n08421/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n08w27/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n08w27/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0b02r/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0b42s/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0b42s/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0bh2w/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0bh2w/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0c431/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0c431/T:
Permission denied
find: ./private/var/fol

Re: User documentation vs Official Docs

[Rob Sargent]

On 07/19/2018 11:04 AM, Peter J. Holzer wrote:

On 2018-07-18 08:09:35 +1000, Tim Cross wrote:

If using web widgets to author content on the wiki is the main
impediment for contributing content, maybe we should see if the wiki
provides alternative access methods. I've used wikis in the past which
allowed users to upload content via xmlrpc, api etc. Perhaps something
similar could be made available for those making significant
contributions or to a select few 'curators' who could accept content
from others.

There are also browser plugins like It's all text, textern, wasavi, etc.
which allow the user to use a real text editor instead of a text area.

 hp

Keep in mind that Chrome broke "It's all text" compatibility, at least 
with emacs and now it's done via "Edit with Emacs".  This in my 
experience is a step backwards from "It's all text".

Re: User documentation vs Official Docs

[Rob Sargent]

On 07/19/2018 06:58 PM, Adrian Klaver wrote:

On 07/19/2018 05:54 PM, Adrian Klaver wrote:

On 07/19/2018 05:43 PM, Melvin Davidson wrote:








 > Then again people might use shared, university or library computers
Would you please be so kind as to inform us which university or 
library allows users to install software on a _shared_ computer.


Pretty sure Ken was referring to looking up documentation, not 
running Postgres.


Or on computer lab machines.



And using psql on such available devices.  Hopefully this new set of 
docs will have something for that usage too?




BTW, since you mention library, that is an excellent way to have the 
books ordered and shared.>FOR FREE<.  AFAIK, all that is required is 
for
someone to request the library purchase the book, to be used for 
shared learning.



--
*Melvin Davidson**
Maj. Database & Exploration Specialist**
Universe Exploration Command – UXC***
Employment by invitation only!

Re: User documentation vs Official Docs

[Rob Sargent]

On 07/20/2018 05:48 PM, Joshua D. Drake wrote:

On 07/20/2018 03:59 PM, Alvaro Herrera wrote:


I don't see why we need this thread to continue.  This sounds like
somebody looking for a solution when they don't yet know what the
problem is.


Unfortunately, you don't understand the problem which is why this 
thread is happening on -general and not -hackers. The problem is 
simple as illustrated, our user documentation is less than stellar for 
those trying to solve specific problems. This isn't a new problem nor 
is it one that is not communicated. I hear the issue from users ever 
single time I speak or attend a conference/meetup.


I was hoping to get the -general community to step and build some 
recipes and howto articles without at the same time dictating the 
solution. That's a good thing because a non-dictated solution is 
likely to have more strength.


The wiki is a terrible choice but if that is where the community 
thinks it should go, I welcome people starting to contribute in a 
structured fashion to the wiki. I hope it works out well.


JD

I trust you took notes of specific things user were seeking. Lay those 
out, here or else where, and see if anyone steps up to answer something 
specific.  The majority of the audience here (me seriously not included) 
is NOT looking for how-tos. Generally given the chance on this list, 
members of that majority step in and provide very specific answers to 
specific questions (and sometime general notions are addressed as well). 
They, that majority, simply don't know what isn't obvious - until it 
isn't to someone.

alter table docs

[Rob Sargent]

I was just looking up alter table add constraint syntax under 
"current(10)" and we get


    ADD /table_constraint/ [ NOT VALID ]
    ADD /table_constraint_using_index/

There is a description below for the using_index version but none for 
the plain version.  There is a block for the plain version on the CREATE 
TABLE page. Should it not also appear in the ALTER TABLE page?

Re: alter table docs

[Rob Sargent]

On 07/30/2018 03:07 PM, Adrian Klaver wrote:

On 07/30/2018 09:57 AM, Rob Sargent wrote:
I was just looking up alter table add constraint syntax under 
"current(10)" and we get


     ADD /table_constraint/ [ NOT VALID ]
     ADD /table_constraint_using_index/

There is a description below for the using_index version but none for 
the plain version.  There is a block for the plain version on the CREATE 


I see one:

https://www.postgresql.org/docs/10/static/sql-altertable.html

"ADD table_constraint [ NOT VALID ]

    This form adds a new constraint to a table using the same syntax 
as CREATE TABLE, plus the option NOT VALID, which is currently only 
allowed for foreign key and CHECK constraints. If the constraint is 
marked NOT VALID, the potentially-lengthy initial check to verify that 
all rows in the table satisfy the constraint is skipped. The 
constraint will still be enforced against subsequent inserts or 
updates (that is, they'll fail unless there is a matching row in the 
referenced table, in the case of foreign keys; and they'll fail unless 
the new row matches the specified check constraints). But the database 
will not assume that the constraint holds for all rows in the table, 
until it is validated by using the VALIDATE CONSTRAINT option.

"


TABLE page. Should it not also appear in the ALTER TABLE page?




OK, I was expecting a block in the enclosing text-area for this simple 
form of the command similar to the one for the /using_index/ form.  I 
suppose the existence of the latter lead me to expect the former.  If 
it's as intended I'm fine with that.

Re: alter table docs

[Rob Sargent]

OK, I was expecting a block in the enclosing text-area for this 
simple form of the command similar to the one for the /using_index/ 
form.  I suppose the existence of the latter lead me to expect the 
former.  If it's as intended I'm fine with that.


Aah I see, you where referring to:

"and table_constraint_using_index is:

    [ CONSTRAINT constraint_name ]
    { UNIQUE | PRIMARY KEY } USING INDEX index_name
    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY 
IMMEDIATE ]

"




Exactly.  That that is in the "box" made me think a similar blurb for 
the non-index version should be there also.

Re: alter table docs

[Rob Sargent]

I'm not anxious to see it back-patched.


On 07/30/2018 04:25 PM, Tom Lane wrote:

Rob Sargent  writes:

Exactly.  That that is in the "box" made me think a similar blurb for
the non-index version should be there also.

This seems to have been fixed in v11 but not back-patched.

regards, tom lane

Re: JSONB filed with default JSON from a file

[Rob Sargent]

On 08/13/2018 12:11 PM, Tom Lane wrote:

Merlin Moncure  writes:

On Mon, Aug 13, 2018 at 12:56 PM mrcasa bengaluru  wrote:

Thanks! However, this involves writing the entire JSON in the schema file looks 
inconvenient. I was hoping I would be able to reference to an external JSON 
file which could be used for the default value.


I'm struggling with the notion of default address.  Is the point to get 
a "blank" json structure in place.  Perhaps to fill in later? Otherwise, 
it seems like saying the default name is "Bob"? Rarely the value wanted.

Re: Erroneous behavior of primary key

[Rob Sargent]

> On Aug 27, 2018, at 1:50 PM, Daniel J Peacock  wrote:
> 
> Good afternoon, all.
> I've got an odd situation with a table that has a varchar(255) as the primary 
> key that is getting key values from an Elasticsearch engine.  What I'm 
> finding is that even though there is a primary key on the table, I am getting 
> duplicated keys in the data and the constraint is not blocking these.  When I 
> do a "select ,count(*) from  group by  having 
> count(*) > 1" I get no results.  Yet, when I search the table for a value 
> that is like a key I know to be duplicated, I get multiple results.  When I 
> select from the table where field is equal to the duplicated field I get one 
> result.  I verified that they are distinct row with ctid.  I also created a 
> clone of the table with CTAS and then tried to create a unique index on the 
> id varchar field but that failed with "duplicate keys found".  I'm stumped as 
> to what could be the problem.
> The only thing that I can think of is that the primary key is somehow 
> corrupt.  I've noticed this behavior on other tables on this database.  
> What could be causing this sort of problem?
> 
> Thanks in advance for any insights.
> 
> Dan Peacock
> Auto-wares, Inc.
Check for trailing white space

Re: WARNING: could not flush dirty data: Function not implemented

[Rob Sargent]

On 09/03/2018 12:41 PM, Austin Drenski wrote:
Dmitri Maziuk mailto:dmaz...@bmrb.wisc.edu>> 
wrote:

> Tom Lane mailto:t...@sss.pgh.pa.us>> wrote:
>> Ravi Krishna mailto:sravikris...@aol.com>> writes:
 Whee ... so you get to cope with all the bugs/idiosyncrasies of three
 operating system layers, not just one.  I concur that running Postgres
 in the underlying Windows O/S is probably a much better idea.
>>
>>> Me too, but this is purely for learning and I am much more use to 
Linux stack then ... gasp Windows :-)

>>
>> Hmm, so maybe you should install Ubuntu as the native O/S, and when
>> you need Windows, run it inside a VM?
>
> Between windows 10 and ubuntu 18.04, I would take a really close look at 
freebsd myself. Or at least alpine...


As a developer, I regularly work with PostgreSQL in the Windows 
Subsystem for Linux (WSL). In using it for sandboxing and testing, the 
only notable idiosyncrasy that I have encountered is the appearance of 
these warnings on startup.


Unfortunately, workstation OS is not always a choice, but WSL has so 
far offered a productive (and built-in!) option for running 
Linux-based tools on Windows.


I can't imagine running a production server from WSL, but it is a 
refreshingly simple way to spin up local dev databases. It would be 
great to see the community take an interest in supporting PostgreSQL 
in WSL, if only in the context of its use as a development tool.


--
Austin Drenski
I lost here.   If you're forced to run windows as the principle OS run 
windows-based postgres.  If you wish to use the ubuntu as a dev 
environment can you not connect from there to the native Postgres instance.

Re: Sv: Re: How to get shorter SERVER_VERSION in psql-prompt?

[Rob Sargent]

On 09/03/2018 03:42 PM, Alvaro Herrera wrote:

On 2018-Sep-03, Andreas Joseph Krogh wrote:


select setting as server_version from pg_settings where name =
'server_version';
  ┌──┐
  │  server_version  │
  ├──┤
  │ 10.5 (Ubuntu 10.5-1.pgdg18.04+1) │
  └──┘

Ugh.  (So this is coming from "configure --with-extra-version" stuff)

I guess you could just split it out at the first whitespace ...


Does that also diddle the value of "server_version_num"?

bad url in docs

[Rob Sargent]

Version 10

33.18. SSL Support

The pointers off to hp.com seem to have gone away on or about 
28Aug2018.  They also fall under the heading of HP OpenVMS Systems Doc 
which may explain why they've disappeared.

Re: bad url in docs

[Rob Sargent]

Sorry.  I didn't see the specific form for documentations issues.



On 09/06/2018 04:52 PM, Rob Sargent wrote:

Version 10

33.18. SSL Support

The pointers off to hp.com seem to have gone away on or about 
28Aug2018.  They also fall under the heading of HP OpenVMS Systems Doc 
which may explain why they've disappeared.

Re: Select rows when all all ids of its children records matches

[Rob Sargent]

On 09/12/2018 10:08 AM, Arup Rakshit wrote:

I tried :

WITH posts_tags_cte AS (
        SELECT post_id, array_agg(tag_id) as tags
        FROM posts_tags
        WHERE tag_id in (1, 2)
        GROUP BY post_id
)
SELECT posts.id  FROM posts_tags_cte JOIN posts ON 
posts.id  = posts_tags_cte.post_id

WHERE posts_tags_cte.tags @> array[1, 2]::int8[]

But it gives me all the posts.



Aren't you looking for cte.tags = array[1,2]?

Re: Can I add Index to make a query faster which involves joins on unnest ?

[Rob Sargent]

> On Sep 13, 2018, at 12:17 PM, Arup Rakshit  wrote:
> 
> The below query basically gives the result by maintaining the order of the 
> sizes in the list.
> 
> explain analyze select
> "price_levels"."name",
> "price_levels"."size"
> from
> "price_levels"
> join unnest(array['M',
> 'L',
> 'XL',
> '2XL',
> '3XL',
> '4XL',
> '5XL',
> '6XL',
> 'S']) with ordinality t(size,
> ord)
> using (size)
> order by
> t.size
> 
> 
> I have a Btree index on the size column.
> 
> Explain output is:
> 
> Merge Join  (cost=4.61..5165.38 rows=6 width=46) (actual 
> time=0.157..57.872 rows=6 loops=1)
>   Merge Cond: ((price_levels.size)::text = t.size)
>   ->  Index Scan using price_levels_size_idx on price_levels  
> (cost=0.29..4111.05 rows=6 width=14) (actual time=0.044..25.941 
> rows=6 loops=1)
>   ->  Sort  (cost=4.32..4.57 rows=100 width=32) (actual time=0.108..3.946 
> rows=53289 loops=1)
> Sort Key: t.size
> Sort Method: quicksort  Memory: 25kB
> ->  Function Scan on unnest t  (cost=0.00..1.00 rows=100 width=32) 
> (actual time=0.030..0.033 rows=9 loops=1)
> Planning time: 0.667 ms
> Execution time: 62.846 ms
> 
> 
> 
> Thanks,
> 
> Arup Rakshit
> a...@zeit.io 
> 
> 
There are not value of size fit it to be a worthwhile key.
> 

heads up on large text fields.

[Rob Sargent]

Playing around with files-in-text-field.  I can happily slam a 10M file 
into a text field in a table defined as


   gtdb=# \d gt.ld
    Table "gt.ld"
    Column    | Type | Collation | Nullable | Default
   --+--+---+--+-
 id   | uuid |   | not null |
 name | text |   |  |
 markerset_id | uuid |   | not null |
 ld   | text |   |  |
   Indexes:
    "ld_pkey" PRIMARY KEY, btree (id)
   Foreign-key constraints:
    "ld_markerset_id_fkey" FOREIGN KEY (markerset_id) REFERENCES
   base.markerset(id)

   gtdb=# select id, length(ld), substring(ld,  30, 100) from gt.ld;
  id  | length  | substring
   
--+-+--
 28f8dc94-c9d1-4c45-b504-fda585b497f8 | 6742760
   | +
  | | 3 2
   rs1858447|5852230|10+
  | |  0.50
   0.50 +
  | | 3 2
   rs1567706|5853767|10+
  | |  0.50 0.50
   (1 row)

And I can regenerate the file using java (with jOOQ) in respectable time.

However, I get into deep dodo when I try redirecting psql output such as

   select ld from gt.ld\g /tmp/regen.file

"/tmp/regen.file" gets very large, very fast and I have to 
pg_terminate_backend.  Tried this three times, once using "\o test.blob" 
instead.


   h009357:loader$ ls -ltr
   total 2048
   -rwxr-xr-x 1 u0138544 camplab 10002460672 Sep 21 15:49 test.blob

Frankly, I'm suspicious of that ls (it's an smb mount of 25T partition) 
but that's what's in the emacs shell buffer!
The re-direct isn't a must-have, but was hoping that would be an easy 
way to get a file back.


Have I simply gone too far with text type?

   h009357:share$ psql --version
   psql (PostgreSQL) 10.5 (Ubuntu 10.5-0ubuntu0.18.04)

   postgres=# select version();
   version
   
-
 PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
   4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
   (1 row)

Re: heads up on large text fields.

[Rob Sargent]

> On Sep 21, 2018, at 7:59 PM, Andres Freund  wrote:
> 
>> On 2018-09-21 18:28:37 -0600, Rob Sargent wrote:
>> "/tmp/regen.file" gets very large, very fast and I have to
>> pg_terminate_backend.  Tried this three times, once using "\o test.blob"
>> instead.
>> 
>>   h009357:loader$ ls -ltr
>>   total 2048
>>   -rwxr-xr-x 1 u0138544 camplab 10002460672 Sep 21 15:49 test.blob
> 
> I suspect the layouting of such wide columns problably creates a lot of
> pain.  I'd try \copy and doing the query after \a.
> 
> Greetings,
> 
> Andres Freund
The formatting could be an issue for sure: there are a couple of very long 
lines early and late in the file. But my real concern is the unending output 
stream. If I haven’t made some obvious mistake, psql redirect of large text 
values may have an issue.

Re: heads up on large text fields.

[Rob Sargent]

On 09/22/2018 06:00 AM, Andreas Kretschmer wrote:



Am 22.09.2018 um 02:28 schrieb Rob Sargent:

However, I get into deep dodo when I try redirecting psql output such as

    select ld from gt.ld\g /tmp/regen.file



works for me if i start psql with -t -A -o /path/to/file
(pg 10.5, but psql from 11beta3)


Regards, Andreas

OK, I'm a little slow on the uptake.  The few very wide lines (728035 
characters) demand that all the other lines be padded and with 132236 
lines you end up with a 96G file (with out the smarts provided about).

Re: Out of Memory

[Rob Sargent]

> On Sep 27, 2018, at 3:45 PM, Laurenz Albe  wrote:
> 
> Christoph Moench-Tegeder wrote:
>> ## Laurenz Albe (laurenz.a...@cybertec.at):
>> 
>>> vm.overcommit_memory = 2
>>> vm_overcommit_ratio = 100
>>> 
>>> Linux commits (swap * overcommit_ratio * RAM / 100),
>> 
>>  ^
>>  That should be a "+".
> 
> Yes; shame on me for careless typing, and thank you for the
> correction.
Are there any parentheses needed in that formula?

Re: Why my query not using index to sort?

[Rob Sargent]

On 09/28/2018 12:51 PM, Arup Rakshit wrote:


Yes, I have shown the explain plan output. But in my application log 
the sql query prints 1.7 to 1.9 ms.



How often does the production app make this call?  Apparently it could 
do it 500 times per second.  But at such a rate the network overhead 
would be stupendous - you might think about getting more data per call, 
few calls.

COPY threads

[Rob Sargent]

Can anyone here tell me whether or not the CopyManager facility in JDBC 
via org.postgresql:postgresql:42.1.4 is internally multithreaded? 
Running on CentOS 7 (all participants), java8, postgres 10.5

Re: COPY threads

[Rob Sargent]

> On Oct 10, 2018, at 10:50 AM, Laurenz Albe  wrote:
> 
> Rob Sargent wrote:
>> Can anyone here tell me whether or not the CopyManager facility in JDBC 
>> via org.postgresql:postgresql:42.1.4 is internally multithreaded? 
>> Running on CentOS 7 (all participants), java8, postgres 10.5
> 
> It isn't, and there would not be much reason for it to be, as COPY
> in PostgreSQL cannot be parallelized.
Thank you for that confirmation.  I had all cores firing on my copyManager box 
and it turned out it was really just looking really hard for more byte to free 
up.  I was getting a lot more out of the database than I could chew.  OOM 
ensued.

Cheers,
rjs

Re: COPY threads

[Rob Sargent]

> On Oct 10, 2018, at 1:24 PM, Andres Freund  wrote:
> 
> On 2018-10-10 18:50:02 +0200, Laurenz Albe wrote:
>> Rob Sargent wrote:
>>> Can anyone here tell me whether or not the CopyManager facility in JDBC 
>>> via org.postgresql:postgresql:42.1.4 is internally multithreaded? 
>>> Running on CentOS 7 (all participants), java8, postgres 10.5
>> 
>> It isn't, and there would not be much reason for it to be, as COPY
>> in PostgreSQL cannot be parallelized.
> 
> s/cannot/is not/.
> 
> You obviously can just copy the data into postgres over multiple
> connections if you need to speed COPY up. But that requires splitting up
> the data on the clientside.
> 
> Greetings,
> 
> Andres Freund
Interesting note, deeply pocketed.  The COPY portion is /not/ my problem - 
that’s plenty fast enough and memory efficient enough straight from the box.  I 
had over looked that I was requesting 33M records, in a map with a longish CSV 
string as key...then playing sort games with keys and ... well that’s as stupid 
as I got.

Re: Advice on logging strategy

[Rob Sargent]

> On Oct 11, 2018, at 4:26 AM, Mike Martin  wrote:
> 
> I have a question on logging strategy
> 
> I have loggin set to
> log_statement = 'all' on a network database with logging set to csv so I can 
> import it to a logging table
> 
> However the database is populated via a nightly routine downloading data via 
> REST APIusing prepared statements
> 
> This results in enormous log files which take ages to import using copy 
> becuase each execute statement is logged with the parameters chosen
> 
> Is there any way around this?
> 
> I cant find any way to filter dml statements
> 
> thanks
> 
Do you want all the log lines in you logging table?
There was a thread yesterday (10.Oct.2018) on COPY which mention the 
possibility of multiple processes COPYing to same table.

judging acceptable discrepancy in row count v. estimate

[Rob Sargent]

Should reality be half again as large as the estimated row count?

coon=# select count(*) from sui.segment;
  count   
--
 49,942,837  -- my commas
(1 row)

coon=# vacuum (analyse, verbose) sui.probandset;
INFO:  vacuuming "sui.probandset"
INFO:  scanned index "probandset_pkey" to remove 3122 row versions
DETAIL:  CPU: user: 4.70 s, system: 1.45 s, elapsed: 26.97 s
INFO:  scanned index "probandsetunique" to remove 3122 row versions
DETAIL:  CPU: user: 5.99 s, system: 10.24 s, elapsed: 97.42 s
INFO:  "probandset": removed 3122 row versions in 1951 pages
DETAIL:  CPU: user: 0.04 s, system: 0.00 s, elapsed: 0.73 s
INFO:  index "probandset_pkey" now contains 33655227 row versions in 259175 
pages
DETAIL:  3122 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.05 s.
INFO:  index "probandsetunique" now contains 33655227 row versions in 1231894 
pages
DETAIL:  3121 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.05 s.
INFO:  "probandset": found 890 removable, 90624 nonremovable row versions in 
4039 out of 2244646 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 288037
There were 5463 unused item pointers.
Skipped 0 pages due to buffer pins, 564917 frozen pages.
0 pages are entirely empty.
CPU: user: 10.91 s, system: 11.80 s, elapsed: 131.01 s.
INFO:  vacuuming "pg_toast.pg_toast_18165"
INFO:  index "pg_toast_18165_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s.
INFO:  "pg_toast_18165": found 0 removable, 0 nonremovable row versions in 0 
out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 288037
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.03 s.
INFO:  analyzing "sui.probandset"
INFO:  "probandset": scanned 3 of 2244646 pages, containing 448480 live 
rows and 0 dead rows; 3 rows in sample, 33555961 estimated total rows
VACUUM
Time: 535436.137 ms (08:55.436)
coon=# reindex table sui.segment
coon-# ;
REINDEX
Time: 681530.451 ms (11:21.530)
coon=# select count(*) from sui.segment;
  count   
--
 49942837
(1 row)

Re: judging acceptable discrepancy in row count v. estimate

[Rob Sargent]

> On Oct 16, 2018, at 1:01 PM, Tom Lane  wrote:
> 
> Rob Sargent  writes:
>> Should reality be half again as large as the estimated row count?
>> coon=# select count(*) from sui.segment;
>>  count   
>> --
>> 49,942,837  -- my commas
>> (1 row)
> 
>> coon=# vacuum (analyse, verbose) sui.probandset;
> 
> Uh, what does sui.probandset have to do with sui.segment ?
> 
>   regards, tom lane

As the locals say, Oh My Heck. Nothing at all as far as row count is concern.  
Deepest apologies.

Re: judging acceptable discrepancy in row count v. estimate

[Rob Sargent]

> On Oct 16, 2018, at 1:01 PM, Tom Lane  wrote:
> 
> Rob Sargent  writes:
>> Should reality be half again as large as the estimated row count?
>> coon=# select count(*) from sui.segment;
>>  count   
>> --
>> 49,942,837  -- my commas
>> (1 row)
> 
>> coon=# vacuum (analyse, verbose) sui.probandset;
> 
> Uh, what does sui.probandset have to do with sui.segment ?
> 
>   regards, tom lane

In fullness, 

INFO:  analyzing "sui.segment"
INFO:  "segment": scanned 3 of 1019242 pages, containing 147 live rows 
and 0 dead rows; 3 rows in sample, 49942858 estimated total rows
VACUUM
Time: 321934.748 ms (05:21.935)

So, rather accurately estimated (no inserts, deletes) since bogus report.  
Looks like its good to 5+ decimal places, given sufficient records.

select 49942858.0/49942837.0;
?column?

 1.0042048071878656
(1 row)

This table has no variable length columns. I imagine that helps.

Re: Measuring the Query Optimizer Effect: Turning off the QO?

[Rob Sargent]

> On Jul 7, 2019, at 5:22 PM, Tom Mercha  wrote:
> 
> Hi All
> 
> As we know, a query goes through number of stages before it is executed. 
> One of these stages is query optimization (QO).
> 
> There are various parameters to try and influence optimizer decisions 
> and costs. But I wanted to measure the effect of such a stage by turning 
> it off completely and I can't find such a parameter which explicitly 
> does that. Then I could execute a query to get the effect of "QO active 
> and "QO inactive" and compare.
> 
> Obviously, I know well what the results would generally look like but I 
> am just interested in measuring the differences for various types of 
> queries. I am also aware that this is a simple comparison - there are 
> more interesting comparisons to perform with QO tweaks, but right now I 
> am interested in something basic.
> 
> So how would one shut down QO? Or at least, obtaining the guarantee of 
> generating the worst plan possible, ideally without touching many 
> parameters?
> 
> Best,
> Tom

Drop all indices?

Re: Measuring the Query Optimizer Effect: Turning off the QO?

[Rob Sargent]

> On Jul 7, 2019, at 5:49 PM, Tom Mercha  wrote:
> 
> On 08/07/2019 01:46, Rob Sargent wrote:
>> 
>> 
>>> On Jul 7, 2019, at 5:22 PM, Tom Mercha  wrote:
>>> 
>>> Hi All
>>> 
>>> As we know, a query goes through number of stages before it is executed.
>>> One of these stages is query optimization (QO).
>>> 
>>> There are various parameters to try and influence optimizer decisions
>>> and costs. But I wanted to measure the effect of such a stage by turning
>>> it off completely and I can't find such a parameter which explicitly
>>> does that. Then I could execute a query to get the effect of "QO active
>>> and "QO inactive" and compare.
>>> 
>>> Obviously, I know well what the results would generally look like but I
>>> am just interested in measuring the differences for various types of
>>> queries. I am also aware that this is a simple comparison - there are
>>> https://gitlab.com/camplab/jpsgcs interesting comparisons to perform with 
>>> QO tweaks, but right now I
>>> am interested in something basic.
>>> 
>>> So how would one shut down QO? Or at least, obtaining the guarantee of
>>> generating the worst plan possible, ideally without touching many
>>> parameters?
>>> 
>>> Best,
>>> Tom
>> 
>> Drop all indices?
>> 
> 
> Sorry, maybe my question wasn't clear enough.
> 
> A query can be rewritten in various ways by applying rules and costs of 
> relational algebra operators, as well as their parallelisation. I am 
> talking about turning off this query optimization, so I am already 
> assuming that indexes aren't present.

Have you played with any of these settings?

postgres=# select version();
 version
 
-
 PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 
(Red Hat 4.8.5-36), 64-bit
(1 row)

postgres=# select name, setting, unit,short_desc from pg_settings where name ~ 
'para';
  name   | setting | unit | 
short_desc 
-+-+--+
 force_parallel_mode | off |  | Forces use of parallel 
query facilities.
 max_parallel_workers| 16  |  | Sets the maximum number of 
parallel workers that can be active at one time.
 max_parallel_workers_per_gather | 8   |  | Sets the maximum number of 
parallel processes per executor node.
 min_parallel_index_scan_size| 64  | 8kB  | Sets the minimum amount of 
index data for a parallel scan.
 min_parallel_table_scan_size| 1024| 8kB  | Sets the minimum amount of 
table data for a parallel scan.
 parallel_setup_cost | 1000|  | Sets the planner's estimate 
of the cost of starting up worker processes for parallel query.
 parallel_tuple_cost | 0.1 |  | Sets the planner's estimate 
of the cost of passing each tuple (row) from worker to master backend.
 ssl_dh_params_file  | |  | Location of the SSL DH 
parameters file.
(8 rows)

Re: Measuring the Query Optimizer Effect: Turning off the QO?

[Rob Sargent]

> On Jul 7, 2019, at 6:01 PM, Rob Sargent  wrote:
> 
> 
> 
>> On Jul 7, 2019, at 5:49 PM, Tom Mercha > <mailto:merch...@hotmail.com>> wrote:
>> 
>> On 08/07/2019 01:46, Rob Sargent wrote:
>>> 
>>> 
>>>> On Jul 7, 2019, at 5:22 PM, Tom Mercha >>> <mailto:merch...@hotmail.com>> wrote:
>>>> 
>>>> Hi All
>>>> 
>>>> As we know, a query goes through number of stages before it is executed.
>>>> One of these stages is query optimization (QO).
>>>> 
>>>> There are various parameters to try and influence optimizer decisions
>>>> and costs. But I wanted to measure the effect of such a stage by turning
>>>> it off completely and I can't find such a parameter which explicitly
>>>> does that. Then I could execute a query to get the effect of "QO active
>>>> and "QO inactive" and compare.
>>>> 
>>>> Obviously, I know well what the results would generally look like but I
>>>> am just interested in measuring the differences for various types of
>>>> queries. I am also aware that this is a simple comparison - there are
>>>> https://gitlab.com/camplab/jpsgcs <https://gitlab.com/camplab/jpsgcs> 
>>>> interesting comparisons to perform with QO tweaks, but right now I
>>>> am interested in something basic.
>>>> 
>>>> So how would one shut down QO? Or at least, obtaining the guarantee of
>>>> generating the worst plan possible, ideally without touching many
>>>> parameters?
>>>> 
>>>> Best,
>>>> Tom
>>> 
>>> Drop all indices?
>>> 
>> 
>> Sorry, maybe my question wasn't clear enough.
>> 
>> A query can be rewritten in various ways by applying rules and costs of 
>> relational algebra operators, as well as their parallelisation. I am 
>> talking about turning off this query optimization, so I am already 
>> assuming that indexes aren't present.
> 
> Have you played with any of these settings?
> 
> postgres=# select version();
>  version  
>
> -
>  PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 
> (Red Hat 4.8.5-36), 64-bit
> (1 row)
> 
> postgres=# select name, setting, unit,short_desc from pg_settings where name 
> ~ 'para';
>   name   | setting | unit |   
>   short_desc 
> -+-+--+
>  force_parallel_mode | off |  | Forces use of parallel 
> query facilities.
>  max_parallel_workers| 16  |  | Sets the maximum number 
> of parallel workers that can be active at one time.
>  max_parallel_workers_per_gather | 8   |  | Sets the maximum number 
> of parallel processes per executor node.
>  min_parallel_index_scan_size| 64  | 8kB  | Sets the minimum amount 
> of index data for a parallel scan.
>  min_parallel_table_scan_size| 1024| 8kB  | Sets the minimum amount 
> of table data for a parallel scan.
>  parallel_setup_cost | 1000|  | Sets the planner's 
> estimate of the cost of starting up worker processes for parallel query.
>  parallel_tuple_cost | 0.1 |  | Sets the planner's 
> estimate of the cost of passing each tuple (row) from worker to master 
> backend.
>  ssl_dh_params_file  | |  | Location of the SSL DH 
> parameters file.
> (8 rows)
> 
Well not the last one of course.

Re: Measuring the Query Optimizer Effect: Turning off the QO?

[Rob Sargent]

> On Jul 7, 2019, at 6:02 PM, Rob Sargent  wrote:
> 
> 
> 
>> On Jul 7, 2019, at 6:01 PM, Rob Sargent > <mailto:robjsarg...@gmail.com>> wrote:
>> 
>> 
>> 
>>> On Jul 7, 2019, at 5:49 PM, Tom Mercha >> <mailto:merch...@hotmail.com>> wrote:
>>> 
>>> On 08/07/2019 01:46, Rob Sargent wrote:
>>>> 
>>>> 
>>>>> On Jul 7, 2019, at 5:22 PM, Tom Mercha >>>> <mailto:merch...@hotmail.com>> wrote:
>>>>> 
>>>>> Hi All
>>>>> 
>>>>> As we know, a query goes through number of stages before it is executed.
>>>>> One of these stages is query optimization (QO).
>>>>> 
>>>>> There are various parameters to try and influence optimizer decisions
>>>>> and costs. But I wanted to measure the effect of such a stage by turning
>>>>> it off completely and I can't find such a parameter which explicitly
>>>>> does that. Then I could execute a query to get the effect of "QO active
>>>>> and "QO inactive" and compare.
>>>>> 
>>>>> Obviously, I know well what the results would generally look like but I
>>>>> am just interested in measuring the differences for various types of
>>>>> queries. I am also aware that this is a simple comparison - there are
>>>>> https://gitlab.com/camplab/jpsgcs <https://gitlab.com/camplab/jpsgcs> 
>>>>> interesting comparisons to perform with QO tweaks, but right now I
>>>>> am interested in something basic.
>>>>> 
>>>>> So how would one shut down QO? Or at least, obtaining the guarantee of
>>>>> generating the worst plan possible, ideally without touching many
>>>>> parameters?
>>>>> 
>>>>> Best,
>>>>> Tom
>>>> 
>>>> Drop all indices?
>>>> 
>>> 
>>> Sorry, maybe my question wasn't clear enough.
>>> 
>>> A query can be rewritten in various ways by applying rules and costs of 
>>> relational algebra operators, as well as their parallelisation. I am 
>>> talking about turning off this query optimization, so I am already 
>>> assuming that indexes aren't present.
>> 
>> Have you played with any of these settings?
>> 
>> postgres=# select version();
>>  version 
>> 
>> -
>>  PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 
>> 20150623 (Red Hat 4.8.5-36), 64-bit
>> (1 row)
>> 
>> postgres=# select name, setting, unit,short_desc from pg_settings where name 
>> ~ 'para';
>>   name   | setting | unit |  
>>short_desc 
>> -+-+--+
>>  force_parallel_mode | off |  | Forces use of parallel 
>> query facilities.
>>  max_parallel_workers| 16  |  | Sets the maximum number 
>> of parallel workers that can be active at one time.
>>  max_parallel_workers_per_gather | 8   |  | Sets the maximum number 
>> of parallel processes per executor node.
>>  min_parallel_index_scan_size| 64  | 8kB  | Sets the minimum amount 
>> of index data for a parallel scan.
>>  min_parallel_table_scan_size| 1024| 8kB  | Sets the minimum amount 
>> of table data for a parallel scan.
>>  parallel_setup_cost | 1000|  | Sets the planner's 
>> estimate of the cost of starting up worker processes for parallel query.
>>  parallel_tuple_cost | 0.1 |  | Sets the planner's 
>> estimate of the cost of passing each tuple (row) from worker to master 
>> backend.
>>  ssl_dh_params_file  | |  | Location of the SSL DH 
>> parameters file.
>> (8 rows)
>> 
> Well not the last one of course.

Better yet, “where category ~* ‘planner’"

Re: Measuring the Query Optimizer Effect: Turning off the QO?

[Rob Sargent]

> O
> Sorry, maybe my question wasn't clear enough.
> 
> A query can be rewritten in various ways by applying rules and costs of
> relational algebra operators, as well as their parallelisation. I am
> talking about turning off this query optimization, so I am already
> assuming that indexes aren't present.
 
 Have you played with any of these settings?
 
 postgres=# select version();
  version
 -
  PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 
 20150623 (Red Hat 4.8.5-36), 64-bit
 (1 row)
 
 postgres=# select name, setting, unit,short_desc from pg_settings where 
 name ~ 'para';
   name   | setting | unit |
  short_desc
 -+-+--+
  force_parallel_mode | off |  | Forces use of parallel 
 query facilities.
  max_parallel_workers| 16  |  | Sets the maximum 
 number of parallel workers that can be active at one time.
  max_parallel_workers_per_gather | 8   |  | Sets the maximum 
 number of parallel processes per executor node.
  min_parallel_index_scan_size| 64  | 8kB  | Sets the minimum 
 amount of index data for a parallel scan.
  min_parallel_table_scan_size| 1024| 8kB  | Sets the minimum 
 amount of table data for a parallel scan.
  parallel_setup_cost | 1000|  | Sets the planner's 
 estimate of the cost of starting up worker processes for parallel query.
  parallel_tuple_cost | 0.1 |  | Sets the planner's 
 estimate of the cost of passing each tuple (row) from worker to master 
 backend.
  ssl_dh_params_file  | |  | Location of the SSL DH 
 parameters file.
 (8 rows)
 
>>> Well not the last one of course.
>> 
>> Better yet, “where category ~* ‘planner’"
>> 
> 
> Hi
> 
> Yes, I've taken a look. I'm just interested in turning off the whole 
> module and there is no parameter to do just that - an on and off switch 
> - from what I can understand. What I can do instead is to go over the 
> entire list of parameters and configure them each to generate a bad 
> plan, but I'm not sure in general how to make a configuration which 
> gives me the worst plan possible under all situations.
> 
> I was hoping that someone has the on/off switch I'm looking for or a 
> parameter template which has been used for the same purpose or something 
> along these lines...
> 
> This document describes the module I'm talking about: 
> https://www.postgresql.org/docs/current/planner-optimizer.html 
> 
Yeah, I guess I read that to say postgres will optimize generically (after 
geqo_threshold). Now maybe that’s the worst plan? Per force a lot of seq-scans 
in the absence of any indices.  And if it isn’t the worst possible plan, so 
what, you’re using postgres, you won’t get a worse plan without lying to the 
optimizer.
But I’m clearly in over my head.  The pros will be along shortly, I’m sure.

Re: Measuring the Query Optimizer Effect: Turning off the QO?

[Rob Sargent]

> On Jul 7, 2019, at 6:29 PM, Rob Sargent  wrote:
> 
> 
> 
>> O
>>>>>> Sorry, maybe my question wasn't clear enough.
>>>>>> 
>>>>>> A query can be rewritten in various ways by applying rules and costs of
>>>>>> relational algebra operators, as well as their parallelisation. I am
>>>>>> talking about turning off this query optimization, so I am already
>>>>>> assuming that indexes aren't present.
>>>>> 
>>>>> Have you played with any of these settings?
>>>>> 
>>>>> postgres=# select version();
>>>>>  version
>>>>> -
>>>>>  PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 
>>>>> 20150623 (Red Hat 4.8.5-36), 64-bit
>>>>> (1 row)
>>>>> 
>>>>> postgres=# select name, setting, unit,short_desc from pg_settings where 
>>>>> name ~ 'para';
>>>>>   name   | setting | unit |   
>>>>>   short_desc
>>>>> -+-+--+
>>>>>  force_parallel_mode | off |  | Forces use of 
>>>>> parallel query facilities.
>>>>>  max_parallel_workers| 16  |  | Sets the maximum 
>>>>> number of parallel workers that can be active at one time.
>>>>>  max_parallel_workers_per_gather | 8   |  | Sets the maximum 
>>>>> number of parallel processes per executor node.
>>>>>  min_parallel_index_scan_size| 64  | 8kB  | Sets the minimum 
>>>>> amount of index data for a parallel scan.
>>>>>  min_parallel_table_scan_size| 1024| 8kB  | Sets the minimum 
>>>>> amount of table data for a parallel scan.
>>>>>  parallel_setup_cost | 1000|  | Sets the planner's 
>>>>> estimate of the cost of starting up worker processes for parallel query.
>>>>>  parallel_tuple_cost | 0.1 |  | Sets the planner's 
>>>>> estimate of the cost of passing each tuple (row) from worker to master 
>>>>> backend.
>>>>>  ssl_dh_params_file  | |  | Location of the SSL 
>>>>> DH parameters file.
>>>>> (8 rows)
>>>>> 
>>>> Well not the last one of course.
>>> 
>>> Better yet, “where category ~* ‘planner’"
>>> 
>> 
>> Hi
>> 
>> Yes, I've taken a look. I'm just interested in turning off the whole 
>> module and there is no parameter to do just that - an on and off switch 
>> - from what I can understand. What I can do instead is to go over the 
>> entire list of parameters and configure them each to generate a bad 
>> plan, but I'm not sure in general how to make a configuration which 
>> gives me the worst plan possible under all situations.
>> 
>> I was hoping that someone has the on/off switch I'm looking for or a 
>> parameter template which has been used for the same purpose or something 
>> along these lines...
>> 
>> This document describes the module I'm talking about: 
>> https://www.postgresql.org/docs/current/planner-optimizer.html 
>> <https://www.postgresql.org/docs/current/planner-optimizer.html>
> Yeah, I guess I read that to say postgres will optimize generically (after 
> geqo_threshold). Now maybe that’s the worst plan? Per force a lot of 
> seq-scans in the absence of any indices.  And if it isn’t the worst possible 
> plan, so what, you’re using postgres, you won’t get a worse plan without 
> lying to the optimizer.
> But I’m clearly in over my head.  The pros will be along shortly, I’m sure.

And I didn’t notice I had pasted a url to a git project. Totally irrelevant to 
the issue at hand.  My apologies.

Re: How to run a task continuously in the background

[Rob Sargent]

> On Jul 17, 2019, at 1:26 AM, Dirk Mika  wrote:
> 
>  
> We used a trigger that called pg_notify 
> (https://www.postgresql.org/docs/9.5/sql-notify.html 
> ​) and then had another
> 
> process that LISTENed for notifications.
> 
>  
> 
> What kind of process is this? I'm assuming that this is an application 
> written in C.
> 
>  
> 
> The advantage of LISTEN / NOTIFY is only that the process which should 
> process data does not have to do polling, but is notified when there is 
> something to do.
> 
>  
> 
> Dirk
> 
Also, the NOTIFY wouldn’t significantly extend the lifetime or impact of the 
trigger.
>  
> 
> -- 
> Dirk Mika
> Software Developer
> 
> 
> 
> mika:timing GmbH
> Strundepark - Kürtener Str. 11b
> 51465 Bergisch Gladbach
> Germany
> 
> 
> fon +49 2202 2401-1197 
> dirk.m...@mikatiming.de
> www.mikatiming.de
> 
> 
> AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884 
> Geschäftsführer: Harald Mika, Jörg Mika
> 
>  
> 

Re: Rearchitecting for storage

[Rob Sargent]

> 
> That would likely keep the extra storage requirements small, but still 
> non-zero.  Presumably the upgrade would be unnecessary if it could be done 
> without rewriting files.  Is there any rule of thumb for making sure one has 
> enough space available for the upgrade?   I suppose that would come down to 
> what exactly needs to get rewritten, in what order, etc., but the pg_upgrade 
> docs don't seem to have that detail.  For example, since we've got an ~18TB 
> table (including its indices), if that needs to be rewritten then we're still 
> looking at requiring significant extra storage.  Recent experience suggests 
> postgres won't necessarily do things in the most storage-efficient way.. we 
> just had a reindex on that database fail (in --single-user) because 17TB was 
> insufficient free storage for the db to grow into.
> 
Can you afford to drop and re-create those 6 indices?

Re: Running concurrent txns and measuring the timings in Postgres

[Rob Sargent]

> On Jul 24, 2019, at 1:22 PM, Souvik Bhattacherjee  wrote:
> 
> > It would help to know what problem you are trying to solve?
> 
> Multiple txns are inserting tuples into a table concurrently. Wanted to 
> measure 
> the total time taken to complete the insertion process. Some txns overlap 
> with 
> others on the tuples they insert. Duplicate tuples are not inserted.
> -- 
> adrian.kla...@aklaver.com 
Start both/all clients at approximately the same time, each firing a 
transaction at some believable interval. (Or separate threads with separate db 
connections.)  This should generate the concurrency load I think you’re looking 
for. You can easily time the iteration; actual details on server side would 
likely involve turning on maximum logging, with client identifier, and 
analyzing the logs.

Re: Which version to upgrade upto

[Rob Sargent]

On 7/31/19 9:57 AM, Vikas Sharma wrote:
The architects and developers have perception that the latest release 
always will have bugs and others might be using in production. They 
feel 11.2 will be better bet than 11.4.




Except of course for the bugs fixed in .3 and .4.

Re: pg_wal fills up on big update query

[Rob Sargent]

> On Aug 7, 2019, at 7:34 AM, Daniel Fink (PDF)  wrote:
> 
> Hi all,
>  
> I have a migration where I
> · Add a new nullable column to a table
> · update almost every row in this big table (8 million rows) from 
> another table where I set this new column
>  
> I have also a replication setup running.
> The database has a size of around 20GB.
> While the migration is running, it more than doubles is size and fills up all 
> space.
> Then the migration fails and is rolled back.
>  
> What is the best way of keeping this from happening?
> My current idea is to lock both tables completely from access (the queried 
> and the updated one) so that postgresql does not have to ensure isolation for 
> concurrent queries by keeping a copy of each row.
> Is my thinking here correct?
>  
> Thanks in advance and Best Regards,
> 
Do the update in small chunks

Re: Input validation

[Rob Sargent]

On 8/7/19 12:07 PM, stan wrote:

  Have table that contains employee keys, paired up with work type keys
  (both foreign keys) and a 3rd column that you enter a billing rate in.
  Then I have a table where employees enter their work. I need to validate
  that the employee, work type pair exists, before allowing the new record
  to be inserted.

  Any thoughts as to good way to do this?


Does the employee interactively specify the "work type" then some 
time-spent value?


Can the work-type be chosen from a drop-down generated by

    select work_type from table where employee = 

Otherwise you'll need a trigger on the insert into "enter their work" 
table.  Sad thing here is the user has likely left the scene.

Re: Recomended front ends?

[Rob Sargent]

On 8/7/19 1:38 PM, Adrian Klaver wrote:

On 8/7/19 11:57 AM, stan wrote:

I am in the process of defining an application for a very small company
that uses Postgresql for the backend DB. This DB will eventually run 
on a
hosted machine. As you imagine all of the employees have Windows 
machines
for their normal work asks. Frankly I am not very strong on Windows. 
so I

am wondering what the consensus is for creating forms and reports?

My first though is Libre Office as that is cross platform, and i can 
test
on my development Linux machine. However, i am getting a bit of 
push-back

from the user as he is having issues with installing Libre Office on his
computer. he says it does not play well with MS Office. Also we seem 
to be

having some bugs with Libre Office Base in early development.


Yeah, I gave up on Base awhile back due to its flaky performance.



What is the community wisdom here?



What I have done is gone the Web route. In my case using Django as the 
framework/backend and the users browsers as the clients. That greatly 
simplifies keeping up with changes on the client end. There is still a 
need to deal with cross browser issues, but that is less of a chore. 
As Igor's post said it comes down to what you are comfortable with.




And if you choose to go down the web route, and you're not intimately 
familiar with at least one of the strands, get help immediately. There 
is much, much magic involved.

Re: Guidance needed on an alternative take on common prefix SQL

[Rob Sargent]

On 8/7/19 3:36 AM, Laura Smith wrote:

On Wednesday, August 7, 2019 2:01 AM, Andy Colson  wrote:


On 8/6/19 6:25 PM, Laura Smith wrote:


Hi,
I've seen various Postgres examples here and elsewhere that deal with the old 
common-prefix problem (i.e. "given 1234 show me the longest match").
I'm in need of a bit of guidance on how best to implement an alternative take. 
Frankly I don't quite know where to start but I'm guessing it will probably 
involve CTEs, which is an area I'm very weak on.
So, without further ado, here's the scenario:
Given an SQL filtering query output that includes the following column:
87973891
87973970
87973971
87973972
87973973
87973975
87973976
87973977
87973978
87973979
8797400
The final output should be further filtered down to:
87973891
8797397
8797400
i.e. if $last_digit is present 0–9 inclusive, recursively filter until the 
remaining string is all the same (i.e. in this case, when $last_digit[0-9] is 
removed, 8797397 is the same).
So, coming back to the example above:
8797397[0-9] is present
so the "nearest common" I would be looking for is 8797397 because once [0-9] is 
removed, the 7 is the same on the preceeding digit.
The other two rows ( 87973891 and 8797400) are left untouched because 
$last_digit is not present in [0-9].
Hope this question makes sense !
Laura

Hows this?

select distinct
case cc
when 1 then num
else left(num,-1)
end
from (
select
num,
(select count(*) as cc from numbers n2 where left(n2.num, -1) = 
left(numbers.num, -1))
from numbers
) as tmpx ;

-Andy



Hi Andy,

That looks supremely clever !

I have just done a quick test and looks like it works as intended. Will do some 
more thorough testing with a larger dataset in due course.

Thank you very much indeed

Laura




If the target field is really an integer type and you have lots of rows 
you might be better off with arithmetic functions.



create table short as select id/10 as base, array_agg(mod(id,10)) as 
odds from head group by base;


select * from short;
  base   |    odds
-+-
  879740 | {0}
 8797389 | {1}
 8797397 | {0,1,2,3,5,6,7,8,9}
(3 rows)

 select case when array_length(odds,1) = 1 then 10*base + odds[1] else 
base end from short;

   base
--
  8797400
 87973891
  8797397
(3 rows)

Re: pg_wal fills up on big update query

[Rob Sargent]

flyway is an excellent tool, I use it too.  Just make a series of 
migrations.


On 8/9/19 9:03 AM, Daniel Fink (PDF) wrote:


Hi Rob,

Thanks, I will try.

It’s a bit of a  bummer though, because I just started to use flywaydb 
to manage migrations, and it wraps all migrations into a single 
transaction.


So I have to do this outside of the tool.

Best Regards,

Daniel

*From:* Rob Sargent [mailto:robjsarg...@gmail.com 
<mailto:robjsarg...@gmail.com>]

*Sent:* Wednesday, August 7, 2019 4:22 PM
*To:* Daniel Fink (PDF) mailto:daniel.f...@pdf.com>>
*Cc:* pgsql-general@lists.postgresql.org 
<mailto:pgsql-general@lists.postgresql.org>

*Subject:* Re: pg_wal fills up on big update query


On Aug 7, 2019, at 7:34 AM, Daniel Fink (PDF) <mailto:daniel.f...@pdf.com>> wrote:


Hi all,

I have a migration where I

·Add a new nullable column to a table

·update almost every row in this big table (8 million rows) from
another table where I set this new column

I have also a replication setup running.

The database has a size of around 20GB.

While the migration is running, it more than doubles is size and
fills up all space.

Then the migration fails and is rolled back.

What is the best way of keeping this from happening?

My current idea is to lock both tables completely from access (the
queried and the updated one) so that postgresql does not have to
ensure isolation for concurrent queries by keeping a copy of each row.

Is my thinking here correct?

Thanks in advance and Best Regards,

Do the update in small chunks


/This message may contain confidential and privileged information. If 
it has been sent to you in error, please reply to advise the sender of 
the error and then immediately permanently delete it and all 
attachments to it from your systems. If you are not the intended 
recipient, do not read, copy, disclose or otherwise use this message 
or any attachments to it. The sender disclaims any liability for such 
unauthorized use. PLEASE NOTE that all incoming e-mails sent to PDF 
e-mail accounts will be archived and may be scanned by us and/or by 
external service providers to detect and prevent threats to our 
systems, investigate illegal or inappropriate behavior, and/or 
eliminate unsolicited promotional e-mails (“spam”). If you have any 
concerns about this process, please contact us at 
//legal.departm...@pdf.com/ <mailto:legal.departm...@pdf.com>/./ 

Re: Generate test data inserts - 1GB

[Rob Sargent]

-- 
Adrian Klaver

adrian.kla...@aklaver.com 


Thanks for the reply Adrian.

Missed one requirement. Will these methods generate wal logs needed 
for replication?


Actually the data is to check if replication catches up. Below is 
scenario :


1. Have a master slave cluster with replication setup

2. Kill master so that standby takes over. We are using pacemaker for 
auto failure.

Insert 1 GB data in new master while replication is broken.

3 Start oldnode as standby and check if 1GB data gets replicated.

As such testing might be frequent we needed to spend minimum time in 
generating data.

Master slave are in same network.

Thanks !
How quickly does your production instance grow, in terms of 
GB/per-unit-time?

Re: Probably a newbie question

[Rob Sargent]

> 
> I'd look here:
> 
>   SELECT
>   name
>   FROM
>   vendor
>   WHERE
>   bom_item.vendor_key =
>   (
>   SELECT
>   vendor_key
>   FROM
>   mfg_vendor_relationship
>   WHERE
>   bom_item.mfg_key = mfg_key
>   AND
>   prefered = TRUE
>   AND
>   bom_item.project_key = project_key
>   
>   )
> 
> 
> -- 
> Angular momentum makes the world go ‘round.

You might get away with adding
group by vendor_key
if it turns out you’re simply getting many copies of vendor key from that inner 
select.
Run it alone to see.

Re: Bulk Inserts

[Rob Sargent]

> On Aug 10, 2019, at 8:47 PM, Souvik Bhattacherjee  wrote:
> 
> Hi Adrian,
> 
> Thanks for the response.
> 
> > Yes, but you will some code via client or function that batches the 
> > inserts for you.
> 
> Could you please elaborate a bit on how EXP 1 could be performed such that it 
> uses bulk inserts?
> 
> Best,
> -SB
> 
> On Fri, Aug 9, 2019 at 7:26 PM Adrian Klaver  > wrote:
> On 8/9/19 3:06 PM, Souvik Bhattacherjee wrote:
> > Hi,
> > 
> > I'm trying to measure the performance of the following: Multiple txns 
> > inserting tuples into a table concurrently vs single txn doing the whole 
> > insertion.
> > 
> > *new table created as:*
> > create table tab2 (
> > id serial,
> > attr1 integer not null,
> > attr2 integer not null,
> > primary key(id)
> > );
> > 
> > *EXP 1: inserts with multiple txn:*
> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1 where 
> > attr2 = 10);
> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1 where 
> > attr2 = 20);
> > 
> > note: attr2 has only two values 10 and 20
> > 
> > *EXP 2: inserts with a single txn:*
> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1);
> > 
> > I also performed another experiment as follows:
> > *EXP 3:* select attr1, attr2 into tab2 from tab1;
> > 
> > The observation here is EXP 3  is much faster than EXP 2 probably due to 
> > bulk inserts used by Postgres. However I could not find a way to insert 
> > id values in tab2 using EXP 3. Also select .. into .. from .. throws an 
> > error if we create a table first and then populate the tuples using the 
> > command.
> 
> Yes as SELECT INTO is functionally the same as CREATE TABLE AS:
> 
> https://www.postgresql.org/docs/11/sql-selectinto.html 
> 
> 
> > 
> > I have the following questions:
> > 1. Is it possible to have an id column in tab2 and perform a bulk insert 
> > using select .. into .. from .. or using some other means?
> 
> Not using SELECT INTO for reasons given above.
> Though it is possible to SELECT INTO as you show in EXP 3 and then:
> alter table tab2 add column id serial primary key;
> EXP 2 shows the other means.
> 
> > 2. If a table is already created, is it possible to do bulk inserts via 
> > multiple txns inserting into the same table (EXP 3)?
> 
> Yes, but you will some code via client or function that batches the 
> inserts for you.
> 
> > 
> > Best,
> > -SB
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com 
Top-posting (i.e. putting your reply at the top is discouraged here)
Does this appeal to you:
COPY (SELECT * FROM relation) TO ... 
(https://www.postgresql.org/docs/10/sql-copy.html 
)

Re: Quoting style (was: Bulk Inserts)

[Rob Sargent]

Sorry.  I thought I had cut most of the redundancy 

> On Aug 11, 2019, at 2:26 AM, Peter J. Holzer  wrote:
> 
>> On 2019-08-10 21:01:50 -0600, Rob Sargent wrote:
>>On Aug 10, 2019, at 8:47 PM, Souvik Bhattacherjee 
>>wrote:
>> 
>>Hi Adrian,
>> 
>>Thanks for the response.
>> 
>>> Yes, but you will some code via client or function that batches the 
>>> inserts for you.
>> 
>>Could you please elaborate a bit on how EXP 1 could be performed such that
>>it uses bulk inserts?
>> 
>>Best,
>>-SB
>> 
>>On Fri, Aug 9, 2019 at 7:26 PM Adrian Klaver 
>>wrote:
>> 
> [70 lines of full quote removed]
> 
> 
>> Top-posting (i.e. putting your reply at the top is discouraged here)
> 
> He didn't really top-post. He quoted the relevant part of Adrian's
> posting and then wrote his reply below that. This is the style I prefer,
> because it makes it really clear what one is replying to.
> 
> After his reply, he quoted Adrian's posting again, this time completely.
> I think this is unnecessary and confusing (you apparently didn't even
> see that he quoted something above his reply). But it's not as bad as
> quoting everything below the answer (or - as you did - quoting
> everything before the answer which I think is even worse: If I don't see
> any original content within the first 100 lines or so I usually skip the
> rest).
> 
>hp
> 
> -- 
>   _  | Peter J. Holzer| we build much bigger, better disasters now
> |_|_) || because we have much more sophisticated
> | |   | h...@hjp.at | management tools.
> __/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

Re: Changing work_mem

[Rob Sargent]

On 8/13/19 11:04 AM, rihad wrote:

On 08/13/2019 08:44 PM, rihad wrote:

On 08/13/2019 08:22 PM, Luca Ferrari wrote:

On Tue, Aug 13, 2019 at 5:59 PM rihad  wrote:

[dbname] LOG:  temporary file: path
"base/pgsql_tmp/pgsql_tmp93683.257381", size 594


The setting 'work_mem' is within context 'user', that means it will
affect running sessione unless the session itself has already issued a
SET work_mem to xxx.
So this could be a reason why you don't seem to see any change.

Also keep in mind that work_mem work on a connection basis, so you are
going to possibly see 521MB x num_connections if all your clients are
doig the same kind of sort concurrently, which probably causes
PostgreSQL to go to disk due to memory unavailable.

Hope this helps.
Luca
.


Thanks. The box has 15GB mem free (as in FreeBSD )))

And it hasn't moved a notch after the increase.

No code does SET work_mem=... AFAIK.

My apologies to Mr. Peter but I still think that older processes, 
some of them started a couple of weeks ago, use the older setting.
Sorry, I just decreased work_mem back to 256MB, reloaded, and 
instantly started seeing 82mb temp file creation, not 165mb as was 
usual with work_mem=512MB.


So it indeed was applied immediately.
Really weird figures )


The files are written because work-mem was insufficient to complete the 
task at hand, so some data was flushed to disk, more data read into 
memory, processed and eventually all merged into final result.  Larger 
work-mem means more to flush, but less often.

Re: Missing Trigger after pgdump install

[Rob Sargent]

On 8/16/19 4:45 PM, Susan Hurst wrote:
We're using the 9.5.14 in the sandbox to extract data and objects from 
the pgdump that was created in the 9.5.0 version.  Hope I answered 
your question correctly.  If not, let me know and I'll try again.


Our biggest concern is that there may be other silent issues that we 
have not yet discovered.  Thanks for the info you just provided, 
Adrian.  We'll read up on the path settings to see if we can find an 
answer there.


Sue

---

Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hu...@brookhurstdata.com
Mobile: 314-486-3261

On 2019-08-16 17:29, Adrian Klaver wrote:

On 8/16/19 3:18 PM, Susan Hurst wrote:

Production version:
PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 
20120313 (Red Hat 4.4.7-16), 64-bit


Sandbox version:
"PostgreSQL 9.5.14, compiled by Visual C++ build 1800, 64-bit"



I going to say it has something to do with this:

https://www.postgresql.org/docs/9.5/release-9-5-12.html

"Avoid use of insecure search_path settings in pg_dump and other
client programs (Noah Misch, Tom Lane)"

Are you using the 9.5.0 or 9.5.14 version of pg_dump to dump from the
production server?



In case Adrian has gone hojme I'll chime in:  I believe his question is 
asking which installation/version of pgdump is used in the command you 
provided earlier.  If it's run from the production machine it would 
likely be the 9.5.0 version, but could also be the 9.5.14 version.  All 
depending on the PATH and host and postgres installation.

Re: A user atribute question

[Rob Sargent]

> On Aug 17, 2019, at 9:45 AM, stan  wrote:
> 
> Just starting to expore setting up roles & useres. I ran this statement:
> 
> GRANT CONNECT ON DATABASE stan TO employee;
> 
> But yet \du still reports:
> 
> employee| Cannot login 
> 
> What am I doing wrong?
> 
> -- 
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
>-- Benjamin Franklin
> 
> 
Does the role have the login option set?

Re: SELECT all the rows where id is children of other node.

[Rob Sargent]

> On Aug 19, 2019, at 7:42 PM, pabloa98  wrote:
> 
> Hello,
> 
> I have a huge table (100 million rows) of relations between nodes by id in a 
> Postgresql 11 server. Like this: 
> 
> CREATE TABLE relations (
> pid INTEGER NOT NULL,
> cid INTEGER NOT NULL,
> )
> 
> This table has parent-child relations references between nodes by id. Like:
> 
> pid -> cid
> n1 -> n2
> n1 -> n3
> n1 -> n4
> n2 -> n21
> n2 -> n22
> n2 -> n23
> n22 -> n221
> n22 -> n222
> 
> I would like to get a list of all the nodes being children (direct or 
> indirect) of any other node.
> 
> Example. The children of:
> 
> 1) n3: []  (n3 has not children)
> 2) n22: [n221, n222]  (n22 has 2 children: n221 and n222)
> 3) n1: [n2, n21, n22, n23, n221, n222]  (n1 has 6 children including indirect 
> children).
> 
> this pseudo SQL: 
> 
> SELECT *
> FROM relations
> WHERE has_parent(myId) 
> 
> It can be solved with a recursive function or stored procedure. But that 
> requires several passes. Is it possible to solve it in one pass? Perhaps 
> using some low-level function or join or some index expression or auxiliary 
> columns?
> 
> It is OK to create an index or similar using recursive expressions. However, 
> the SELECT expressions should be solved in one pass because of speed.
> 
> 
> Pablo

Are you asking for just the function (always with a seed Id) or the complete 
transformation in a single select? Would you like the descendants on one line 
(eg n22,[n221,n222])?
I wonder if you might add  n3 -> null to explicitly terminate the hierarchy ?

Re: SELECT all the rows where id is children of other node.

[Rob Sargent]

> On Aug 19, 2019, at 7:42 PM, pabloa98  wrote:
> 
> Hello,
> 
> I have a huge table (100 million rows) of relations between nodes by id in a 
> Postgresql 11 server. Like this: 
> 
> CREATE TABLE relations (
> pid INTEGER NOT NULL,
> cid INTEGER NOT NULL,
> )
> 
> This table has parent-child relations references between nodes by id. Like:
> 
> pid -> cid
> n1 -> n2
> n1 -> n3
> n1 -> n4
> n2 -> n21
> n2 -> n22
> n2 -> n23
> n22 -> n221
> n22 -> n222
> 
> I would like to get a list of all the nodes being children (direct or 
> indirect) of any other node.
> 
> Example. The children of:
> 
> 1) n3: []  (n3 has not children)
> 2) n22: [n221, n222]  (n22 has 2 children: n221 and n222)
> 3) n1: [n2, n21, n22, n23, n221, n222]  (n1 has 6 children including indirect 
> children).
> 
> this pseudo SQL: 
> 
> SELECT *
> FROM relations
> WHERE has_parent(myId) 
> 
> It can be solved with a recursive function or stored procedure. But that 
> requires several passes. Is it possible to solve it in one pass? Perhaps 
> using some low-level function or join or some index expression or auxiliary 
> columns?
> 
> It is OK to create an index or similar using recursive expressions. However, 
> the SELECT expressions should be solved in one pass because of speed.
> 
> 
> Pablo

Back at my desk now, to show the possibilities.

with recursive descendants(parent, child) as 
(select p.p, p.c from kids p where not exists (select 1 from kids c where c.c = 
p.p) group by p.p, p.c
 union all
 select k.* from kids k, descendants d where k.p = d.child)
 select * from descendants;

 parent | child 
+---
  1 | 3
  1 | 2
  1 | 4
  2 |21
  2 |22
  2 |23
 22 |   221
 22 |   222
(8 rows)

with recursive descendants(parent, child) as 
(select p.p, p.c from kids p where not exists (select 1 from kids c where c.c = 
p.p) group by p.p, p.c
 union all
 select k.* from kids k, descendants d where k.p = d.child)
 select d.parent, array_agg(d.child) from descendants d group by d.parent;

 parent | array_agg  
+
  1 | {3,2,4}
 22 | {221,222}
  2 | {21,22,23}
(3 rows)

Re: SELECT all the rows where id is children of other node.

[Rob Sargent]

> On Aug 19, 2019, at 7:42 PM, pabloa98  wrote:
> 
> Hello,
> 
> I have a huge table (100 million rows) of relations between nodes by id in a 
> Postgresql 11 server. Like this: 
> 
> CREATE TABLE relations (
> pid INTEGER NOT NULL,
> cid INTEGER NOT NULL,
> )
> 
> This table has parent-child relations references between nodes by id. Like:
> 
> pid -> cid
> n1 -> n2
> n1 -> n3
> n1 -> n4
> n2 -> n21
> n2 -> n22
> n2 -> n23
> n22 -> n221
> n22 -> n222
> 
> I would like to get a list of all the nodes being children (direct or 
> indirect) of any other node.
> 
> Example. The children of:
> 
> 1) n3: []  (n3 has not children)
> 2) n22: [n221, n222]  (n22 has 2 children: n221 and n222)
> 3) n1: [n2, n21, n22, n23, n221, n222]  (n1 has 6 children including indirect 
> children).
> 
> this pseudo SQL: 
> 
> SELECT *
> FROM relations
> WHERE has_parent(myId) 
> 
> It can be solved with a recursive function or stored procedure. But that 
> requires several passes. Is it possible to solve it in one pass? Perhaps 
> using some low-level function or join or some index expression or auxiliary 
> columns?
> 
> It is OK to create an index or similar using recursive expressions. However, 
> the SELECT expressions should be solved in one pass because of speed.
> 
> 
> Pablo

ooops. didn’t get all generations. sorry

Re: SELECT all the rows where id is children of other node.

[Rob Sargent]

> On Aug 19, 2019, at 7:42 PM, pabloa98  wrote:
> 
> Hello,
> 
> I have a huge table (100 million rows) of relations between nodes by id in a 
> Postgresql 11 server. Like this: 
> 
> CREATE TABLE relations (
> pid INTEGER NOT NULL,
> cid INTEGER NOT NULL,
> )
> 
> This table has parent-child relations references between nodes by id. Like:
> 
> pid -> cid
> n1 -> n2
> n1 -> n3
> n1 -> n4
> n2 -> n21
> n2 -> n22
> n2 -> n23
> n22 -> n221
> n22 -> n222
> 
> I would like to get a list of all the nodes being children (direct or 
> indirect) of any other node.
> 
> Example. The children of:
> 
> 1) n3: []  (n3 has not children)
> 2) n22: [n221, n222]  (n22 has 2 children: n221 and n222)
> 3) n1: [n2, n21, n22, n23, n221, n222]  (n1 has 6 children including indirect 
> children).
> 
> this pseudo SQL: 
> 
> SELECT *
> FROM relations
> WHERE has_parent(myId) 
> 
> It can be solved with a recursive function or stored procedure. But that 
> requires several passes. Is it possible to solve it in one pass? Perhaps 
> using some low-level function or join or some index expression or auxiliary 
> columns?
> 
> It is OK to create an index or similar using recursive expressions. However, 
> the SELECT expressions should be solved in one pass because of speed.
> 
> 
> Pablo
I could not find away to handle the branches but this is more complete.
with recursive descendants (last, children) as 
(select c.c, array[null::int] from kids c where not exists (select 1 from kids 
p where c.c = p.p)
 union all
 select k.p, array[k.c] || l.children from kids k, descendants l where k.c = 
l.last)
 select last, children from descendants where children[1] is not null order by 
last
 last |children 
--+-
1 | {2,22,222,NULL}
1 | {4,NULL}
1 | {2,21,NULL}
1 | {2,23,NULL}
1 | {2,22,221,NULL}
1 | {3,NULL}
2 | {22,221,NULL}
2 | {22,222,NULL}
2 | {21,NULL}
2 | {23,NULL}
   22 | {221,NULL}
   22 | {222,NULL}
(12 rows)

I’ll throw in the towel now

Re: SELECT all the rows where id is children of other node.

[Rob Sargent]

> On Aug 21, 2019, at 3:35 AM, Francisco Olarte  wrote:
> 
> Pablo:
> 
> On Tue, Aug 20, 2019 at 6:49 PM pabloa98  wrote:
>> Thank you for your responses Rob. Appreciated. The problem with recursive 
>> queries is that they are executed several times and it has and impact in 
>> performance.
>> I need a subset of those rows and I want them in one pass.
>> I discovered that ltree extension could be useful. I will play with it 
>> today. I am sure there's a way to find al the nodes in O(n) time with n = 
>> size of the resulset ...
> 
> Unless you have some extra conditions in a table ( like
> "autoincremented immutable primary key and parents are always created
> before childs" ) I think your problem of "get all the descendant ( i
> do not like to call them children ) nodes of a given id" can not be
> solved in one pass.
> 
> I mean, if you are getting descendants of the node N1, you need to
> read the last node, NL, of the table to know if it is a child of N1.
> But then you have to read the table again to find childs of NL.
> 
> Of course, if you have something like "hierarchical ids" you can
> traverse ordering by it and know NL MUST be childless, and build the
> tree rooted on node N1 as you go, but without some of this conditions
> I do not think it can be done in an "ideal" db ( which lets you scan
> in any order you can define from just a row without cost ) in one scan
> ( storing and prunning the whole tree as you go is cheating ).
> 
> Also, if your node ids come from a serial and are immutables, or you
> take a little care when mutating them, you can do it traversing by id,
> but you need a full scan, a recursive query with several index scans
> may easily be faster in wide trees.
> 
> 
> Francisco Olarte.
If you accept Francisco’s thesis then you may be interested in this
with recursive descendants (last, children) as 
(select c.c, array[null::int] from kids c where not exists (select 1 from kids 
p where c.c = p.p)
 union all
 select k.p, array[k.c] || l.children from kids k, descendants l where k.c = 
l.last)
 select a.last, array_agg(distinct(a.kids))as clan from (select last, 
unnest(array_remove(children, null)) as kids from descendants where children[1] 
is not null) as a group by last order by last
 last |   clan   
--+--
1 | {2,3,4,21,22,23,221,222}
2 | {21,22,23,221,222}
   22 | {221,222}
(3 rows)
No comment on performance other than to say that if you are interested in the 
result for a given seed parent then performance would likely correlate with the 
average depth of your lineages.

I believe the ascending order of the members of each clan is completely 
fortuitous unless it’s a consequence of distinct?

Re: Postgres SQLSTATE[08006] [7] timeout expired

[Rob Sargent]

> On Aug 24, 2019, at 4:42 PM, Howard Wells  wrote:
> 
> I have three servers behind a load balancer and a fourth server solely for 
> Postgres 10 database that is not behind the load balancer.  All four are 
> behind the same firewall, with port 5432 open. 
> 
> I have a simple browser-based html form to submit email addresses, usernames 
> and passwords; the form submits through a jQuery program that calls a php 
> program. 
> 
> I can navigate the browser to the IP address of the server that hosts the 
> Postgres database and submit records to the database with no problems.  When 
> I navigate to the site's URL (which goes through the load balancer), I always 
> get "SQLSTATE[08006] [7] timeout expired" after a short delay -- in other 
> words, no connection. 
> 
> The php program is very simple:
> 
>  echo 'Hello ' . htmlspecialchars($_POST["firstname"]) . '!' ;
> echo PHP_EOL;
> 
> $dsn = vsprintf('pgsql:host=%s;port=%s;dbname=%s;user=%s;password=%s', [
> 'host' => 'xxx.xx.xx.xx',
> 'port' => '5432',
> 'dbname' => '[DBNAME]',
> 'user' => '[USERNAME]',
> 'password' => '[PASSWORD]',
> ]);
> 
> echo 'HTTP Referer ' . $_SERVER['HTTP_REFERER'];
> echo PHP_EOL;
> 
> echo 'IP Address ' . $_SERVER["REMOTE_ADDR"];
> echo PHP_EOL;
> 
> try{
> // create a PostgreSQL database connection
> 
>   echo "I'm here now";
>   echo PHP_EOL;
> 
> //ini_set("default_socket_timeout", 300);
> $pdo = new PDO($dsn);
> $pdo->setAttribute(PDO::ATTR_TIMEOUT, 300);
> 
> // display a message if connected to the PostgreSQL successfully
> if($pdo){
> echo "Connected to the database successfully!";
> echo PHP_EOL;
> }
> }catch (PDOException $e){
> // report error message
> echo $e->getMessage();
> exit();
> }
> 
> $pdo = null;
> 
> ?>
> 
> The echo messages are simply debugging progress messages for the browser's 
> dev console. 
> 
> The pg_hba.conf has these lines enabled:
> 
> pg_hba.conf:
> hostall [username]   0.0.0.0/0   trust
> hostall all 0.0.0.0/0   md5
> hostall all  ::/0   md5
> hostall all  allmd5
> 
> The $_SERVER["REMOTE_ADDR"]; line shown in my PHP program above always comes 
> back with the address of the load balancer. 
> 
> I'm new to Postgres, so any help with this connection problem will be very 
> appreciated.  Thanks a lot. 
> 
> 
Which load balancer and what are its configs?   
Is this strictly accurate? "I can navigate the browser to the IP address of the 
server that hosts the Postgres database”. i.e. Something is listening on port 
80 of the database machine?  Or are you sending sql statements through the 
firewall directly to the db?

Re: Postgres SQLSTATE[08006] [7] timeout expired

[Rob Sargent]

> 
>  console.
> The pg_hba.conf has these lines enabled:
> pg_hba.conf:
> hostall [username]   0.0.0.0/0   trust
> hostall all 0.0.0.0/0   md5
> hostall all  ::/0   md5
> hostall all  all

Who is encrypting the password?
> 

Re: For SELECT statement (just a reading one, no 'FOR UPDATE'), is COMMIT or ROLLBACK preferred?

[Rob Sargent]

> On Aug 25, 2019, at 1:09 PM, David Wall  wrote:
> 
> Using the latest PostgreSQL, does it matter if my code does a ROLLBACK or a 
> COMMIT on an non-modifying SELECT statement?  My impression is they'd be the 
> same as nothing is changed and therefore there's nothing to commit or 
> rollback, but wondered if there was any difference in how they are processed 
> by Postgres?
> 
> Thanks,
> David
> 
> 
> 
In interactive psql, both issue a warning that there is no current transaction. 
 What is your auto-commit setting and how is your code sent to the server?

Re: Work hours?

[Rob Sargent]

On 8/27/19 4:59 PM, Adrian Klaver wrote:

On 8/27/19 3:27 PM, stan wrote:

I am just starting to explore the power of PostgreSQL's time and date
functionality. I must say they seem very powerful.

I need to write a function that, given a month, and a year as input 
returns

the "work hours" in that month. In other words something like

8 * the count of all days in the range Monday to Friday) within that
calendar month.

Any thoughts as to the best way to approach this?


Use generate_series:

https://www.postgresql.org/docs/11/functions-srf.html

to generate all the days in the month.

Loop over the days and use EXTRACT:

https://www.postgresql.org/docs/11/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT 



to find the dates with a dow(The day of the week as Sunday (0) to 
Saturday (6)) or isodow(The day of the week as Monday (1) to Sunday (7))

that falls in Mon-Fri and add to counter.



Don't you also need a feed from something like google US holidays 
(assuming OP is stateside)

Re: Selecting rows having substring in a column

[Rob Sargent]

On 8/29/19 8:47 AM, Rich Shepard wrote:

On Thu, 29 Aug 2019, Gary Cowell wrote:


Look at the 'LIKE' function


Gary,

Yes, I thought of like but didn't think to look for it in the postgres
manual.

Thank you very much,

Rich


I've given up on the "likes" in favour of the ~ (tilde) and ~* (tilde 
asterisk) operator.  Way cool, powerful. Not standard sql though (iirc)

Re: Query using 'LIKE' returns empty set

[Rob Sargent]

On 8/29/19 10:39 AM, Rich Shepard wrote:

Next problem is one I've not before encountered.

The .sql file used to import data to the fish_counts table has rows 
such as

this one:

('1237796458250','0','17174','Buchanan Creek','Buchanan Creek trib to 
North

Fork Nehalem River','0-3.25','161980','Unknown','Jack or subadult','Peak
live & dead fish','Spawner Counts','ODFW','2012-01-06','1950-1974',25,
'1950-10-01','1951-01-31','Ground','Actual Physical Counts',0), [Lines 
wrapped in the message only.]


When I submit this query I get no rows returned:

select * from fish_counts where stream_tribs ilike 'Nehalem';
 count_id | loc_id | downstream | upstream | stream_name | 
stream_tribs | r
iver_miles | itis_tsn | production | life_stage | count_type | 
data_categor
y | compiled_by | updated | years | nbr_observations | begin_date | 
end_dat
e | sample_method | calc_method | count_value 
--+++--+-+--+--
---+--++++- 

--+-+-+---+--++ 


--+---+-+-
(0 rows)

What is equally puzzling is when I search the input file using
grep -c -e "Nehalem" fish_counts
0 is returned, the same as the postgres query.

I want to understand what I've done incorrectly.

TIA,

Rich



Are you sure that particular file has the search string?

grep -i nehalem fish_counts

Re: Returning a table from a function, that requires multiple selects?

[Rob Sargent]

> On Aug 30, 2019, at 2:09 PM, Guyren Howe  wrote:
> 
> On Aug 30, 2019, at 13:03 , stan mailto:st...@panix.com>> 
> wrote:
> 
>> I need to encapsulate, what are basically 2 related function calls into a 
>> single
>> function. The result of each of th calls is a date type.
>> 
>> y current thinking is to return a 2 row table with the 2 dates in it. But, I 
>> seem to
>> be having issues getting this to work.
>> 
>> Is it possible for a function to return a table with results from multiple
>> queries?
> 
> You could just return a tuple VALUES(a, b). Or you could define a type to 
> return if you want to get fancy.
Here I you might want VALUE(array[‘heading1’,a], array[‘heading2',b]) unless 
you’re certain you know which date is which.

Re: Returning a table from a function, that requires multiple selects?

[Rob Sargent]

> On Aug 30, 2019, at 2:03 PM, stan  wrote:
> 
> I need to encapsulate, what are basically 2 related function calls into a 
> single
> function. The result of each of th calls is a date type.
> 
> y current thinking is to return a 2 row table with the 2 dates in it. But, I 
> seem to
> be having issues getting this to work.
> 
> Is it possible for a function to return a table with results from multiple
> queries?
> 
> -- 
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
>   -- Benjamin Franklin
> 
> 
If the two queries are identical in returned columns you might be able to use 
UNION:
select f1.* from first_query as f1 UNION select f2.* from second_query as f2;

You can’t do any processing of f1 or f2.

floating point output

[Rob Sargent]

I've found the description of floating point types (here 
), 
but I'm looking for the rationale of the output format, particularly 
with respect to total digits presented (variable in a single select's 
output) and the dropping of a trailing zero (to some implying a loss of 
precision).  Is the code my only guide here?

Re: floating point output

[Rob Sargent]

> On Sep 4, 2019, at 9:14 PM, Adrian Klaver  wrote:
> 
>> On 9/4/19 5:23 PM, Rob Sargent wrote:
>> I've found the description of floating point types (here 
>> <https://www.postgresql.org/docs/10/datatype-numeric.html#DATATYPE-FLOAT>), 
>> but I'm looking for the rationale of the output format, particularly with 
>> respect to total digits presented (variable in a single select's output) and 
>> the dropping of a trailing zero (to some implying a loss of precision).  Is 
>> the code my only guide here?
> 
> Some examples would help explain your concerns.
> 
> Bottom line, if you want precision use numeric.
> 
> 
> 
When at my desk I’ll give example. Actual precision is not the issue. Purely 
cosmetics. 

Re: floating point output

[Rob Sargent]

> On Sep 5, 2019, at 7:32 AM, Rob Sargent  wrote:
> 
> 
> 
>> On Sep 4, 2019, at 9:14 PM, Adrian Klaver  wrote:
>> 
>>> On 9/4/19 5:23 PM, Rob Sargent wrote:
>>> I've found the description of floating point types (here 
>>> <https://www.postgresql.org/docs/10/datatype-numeric.html#DATATYPE-FLOAT>), 
>>> but I'm looking for the rationale of the output format, particularly with 
>>> respect to total digits presented (variable in a single select's output) 
>>> and the dropping of a trailing zero (to some implying a loss of precision). 
>>>  Is the code my only guide here?
>> 
>> Some examples would help explain your concerns.
>> 
>> Bottom line, if you want precision use numeric.
>> 
>> 
>> 
> When at my desk I’ll give example. Actual precision is not the issue. Purely 
> cosmetics.

Below I show a floating point column from a recent query.  

Here's my guess at what's happening in the formatting:
  1) scientific notation if smaller than 1.0e-04 (I’m not in the 
very-much-larger-than-one world)
  2) 15 digits after the most signicant 0
  3) remove trailing zeros

I may be alone in this but I find the presentation messy and that's unusual in 
the PostgreSQL world.  To me the most agregious is the truncation of the zeros. 
 Pick a number of digits and stick with it. One format too.

1 |  8.81189e-05 |
2 |  0.000108911 |
3 |  0.000118812 |
4 | 0.000128999871000129 |
5 | 0.000132499966875008 |
6 | 0.000132999867000133 |
7 | 0.000136922971597714 |
8 | 0.000139499930250035 |
9 | 0.000158809516247166 |
   10 | 0.000170294107629758 |
   11 |  0.000287129 |
   12 | 0.000327999672000328 |
   13 | 0.000628999371000629 |
   14 |  0.000653466 |
   15 | 0.000667999332000668 |
   16 | 0.000681999318000682 |
   17 | 0.000683999316000684 |
   18 | 0.000708999291000709 |
   19 | 0.000722999277000723 |
   20 | 0.000730999269000731 |
   21 |  0.000742575 |
   22 | 0.000753999246000754 |
   23 | 0.000755999244000756 |
   24 | 0.000762999237000763 |
   25 | 0.000765999234000766 |
   26 | 0.000773999226000774 |
   27 | 0.000780999219000781 |
   28 | 0.000785999214000786 |
   29 | 0.000787999212000788 |
   30 | 0.000791999208000792 |
   31 | 0.000793999206000794 |
   32 | 0.000813999186000814 |
   33 | 0.000817999182000818 |
   34 | 0.000826999173000827 |
   35 | 0.000833999166000834 |
   36 | 0.000834999165000835 |
   37 | 0.000838999161000839 |
   38 | 0.000847999152000848 |
   39 | 0.000854999145000855 |
   40 | 0.000855999144000856 |
   41 | 0.000857999142000858 |
   42 |  0.000851486 |
   43 | 0.000864999135000865 |
   44 |  0.000861387 |
   45 | 0.000875999124000876 |
   46 | 0.000882999117000883 |
   47 | 0.000887999112000888 |
   48 | 0.000887999112000888 |
   49 | 0.000904999095000905 |
   50 | 0.000910999089000911 |
   51 | 0.000912999087000913 |
   52 | 0.000912999087000913 |
   53 | 0.000914999085000915 |
   54 | 0.000921999078000922 |
   55 | 0.000922999077000923 |
   56 | 0.000938999061000939 |
   57 |  0.000940595 |
   58 | 0.000967999032000968 |
   59 | 0.000971999028000972 |
   60 | 0.000982999017000983 |
   61 | 0.000995999004000996 |
   62 |0.001000998999001 |
   63 |0.001003998996001 |
   64 |0.001003998996001 |
   65 |  0.00101099898900101 |
   66 |  0.00101199898800101 |
   67 |  0.00101599898400102 |
   68 |  0.00101899898100102 |
   69 |  0.00102199897800102 |
   70 |  0.00102599897400103 |
   71 |  0.00103099896900103 |
   72 |  0.00104999895000105 |
   73 |  0.00106899893100107 |
   74 |  0.00107499892500108 |
   75 |  0.00107599892400108 |
   76 |  0.00108499891500109 |
   77 |  0.00108699891300109 |
   78 |  0.00108799891200109 |
   79 |   0.0011039988960011 |
   80 |  0.00110799889200111 |
   81 |  0.00111099888900111 |
   82 |  0.00111299888700111 |
   83 |  0.00111599888400112 |
   84 |  0.00111699888300112 |
   85 |  0.00111999888000112 |
   86 |  0.00111999888000112 |
   87 |  0.00112099887900112 |
   88 |  0.00116599883400117 |
   89 |  0.00117461448106578 |
   90 |   0.0011788180746529 |
   91 |  0.00118099881900118 |
   92 |   0.0011839994080003 |
   93 |  0.00118827261924794 |
   94 |  0.00118899881100119 |
   95 |   0.0012049987950012 |
   96 |  0.00120622217754733 |
   97 |  0.00120999879000121 |
   98 |  0.00121409079871902 |
   99 |  0.00121481807138018 |
  100 |  0.00122618178102479 |
  101 |  0.00122699877300123 |
  102 |  0.00122899877100123 |
  103 |  0.00123999876000124 |
  104 |  0.00125999874000126 |
  105 |  0.00127766866245761 |
  106 |  0.00128099935950032 |
  107 |  0.00129099870900129 |
  108 |  0.00129199870800129 |
  109 |  0.00129299870700129 |
  110 |  0.00129924967518758 |
  111 |   0.0013019986980013 |
  112 |   0.0013019986980013 |
  113 |  0.00130449934775033 |
  114 |  0.001330998

Re: database "cdf_100_1313" does not exist

[Rob Sargent]

> On Sep 9, 2019, at 7:16 AM, nikhil raj  wrote:
> 
> Hi Dan,
> 
> Still facing the same issue.
> 
> /usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT 
> CONNECT ON DATABASE $DBLIST TO cpupdate"
> ERROR:  database "cdf_100_1313" does not exist
> 
 
Can we see the output of psql’s \l ?

Re: pldbgapi extension

[Rob Sargent]

On 9/17/19 10:16 AM, Adrian Klaver wrote:

On 9/17/19 7:44 AM, Prakash Ramakrishnan wrote:

I installed devel and contrib also but not worked here.


Yeah, --contrib won't help as pldbpgapi is not one of the contrib 
modules as found here:


https://www.postgresql.org/docs/11/contrib.html

The basic issue is you are using two install methods YUM for your 
Postgres server and source build for the pldbpgapi extension. They are 
using different directory structures. I think the best bet for the 
time being is to use the symklink suggestion I posted earlier. Someone 
with more experience with RH may also chime in with a better solution.



If OP is building the extension, why not build postgres too?

Re: PostgreSQL License

[Rob Sargent]

> On Sep 17, 2019, at 4:18 PM, Andreas Joseph Krogh  wrote:
> 
> På tirsdag 17. september 2019 kl. 22:55:02, skrev Peter J. Holzer 
> mailto:hjp-pg...@hjp.at>>:
> On 2019-09-17 14:56:30 +0300, Ashkar Dev wrote:
> > but it is saying (without fee)
> > if I create a database with it to work with Web Application if want to sell 
> > it
> > so the buyer must have the PostgreSQL installed in his device to work 
> > offline
> > right?
> > "Permission to use, copy, modify, and distribute this software and its
> > documentation for any purpose, without fee, and without a written agreement 
> > is
> > hereby granted, provided that the above copyright notice and this paragraph 
> > and
> > the following two paragraphs appear in all copies."
> 
> This means that you don't have to pay a fee or sign a written agreement
> to use, copy, modify, and distribute this software and its documentation
> for any purpose. It doesn't say that you can't charge a fee for
> distributing (although why anybody would pay you for something they can
> download themselves for free I don't know).
> 
> hp
>  
> A rule of thumb is - you can do anything you want with it (the PG software 
> inc. its source), except claim you wrote it, as long as you preserve the 
> original license-file(s).
>  
I take it that the OP has an app/dataset on top of PG he/she wishes to market 
(and protect).  Perfectly legit, no? Not clear if there is a desire to disable 
direct db access.

Re: PostgreSQL License

[Rob Sargent]

On 9/18/19 11:50 AM, Ashkar Dev wrote:

Hi all thanks,
I meant maybe I create a web app with PostgreSQL that work locally for 
example for a pharmacy that stores data by barcode while the DB was 
created by PostgreSQL how I can sell the Database for him, how to 
deliver the product to him can I sell the package that contains web 
app files with PostgreSQL software and the database code?


You can charge the pharmacist for your efforts: your web app (license), 
installing postgres and your schema (somewhere) and the data supporting 
your web-app, any documentation of your web-app and schema, any 
maintenance and support you care to specify. You can copy-right your app 
and schema and data.


You cannot (legitimately) charge the pharmacist for any part PostgresQL.

Re: PostgreSQL License

[Rob Sargent]

> On Sep 18, 2019, at 12:17 PM, Adrian Klaver  wrote:
> 
> On 9/18/19 11:06 AM, Rob Sargent wrote:
>> On 9/18/19 11:50 AM, Ashkar Dev wrote:
>>> Hi all thanks,
>>> I meant maybe I create a web app with PostgreSQL that work locally for 
>>> example for a pharmacy that stores data by barcode while the DB was created 
>>> by PostgreSQL how I can sell the Database for him, how to deliver the 
>>> product to him can I sell the package that contains web app files with 
>>> PostgreSQL software and the database code?
>>> 
>> You can charge the pharmacist for your efforts: your web app (license), 
>> installing postgres and your schema (somewhere) and the data supporting your 
>> web-app, any documentation of your web-app and schema, any maintenance and 
>> support you care to specify. You can copy-right your app and schema and data.
>> You cannot (legitimately) charge the pharmacist for any part PostgresQL.
> 
> Why not?
> 
> Pretty sure that is what third parties are doing with their forks of Postgres.
> 
I see your point, but aren’t they in essence charging for their efforts in 
making, managing their fork.  There are plenty of vendors, for a time I was 
one, who happily apply a schema to which ever database the client supplied 
(mssql, ora, pg in my case).