[GENERAL] plpgsql function with offset - Postgres 9.1

[Patrick B]

Hi guys,

I am running a background task on my DB, which will copy data from tableA
to tableB. For that, I'm writing a PL/PGSQL function which basically needs
to do the following:


   1. Select the data from tableA
   2. The limit will be put when calling the function
   3. insert the selected data on Step 1 onto new table

Question:

   - When I stop it and start it again, how can the query "know" that it
   has already processed some rows so it won't do it twice on the same rows?
   If it stopped on row number 100, I need it to continue on row number 101,
   for example.
   - How can I ask the function to return the number of processed rows?


I can add a column on TableB if needed, but not on tableA.

This is what I've done so far:

select data_copy(500);


CREATE or REPLACE FUNCTION data_copy(rows integer)

RETURNS SETOF bigint AS $$


declare

row record;

offset_num integer;


BEGIN


FOR row IN EXECUTE '

SELECT

id,

path,

name,

name_last,

created_at

FROM

tablea

WHERE

ready = true

ORDER BY 1 LIMIT ' || rows || ' OFFSET ' || rows || ''

LOOP


INSERT INTO tableB (id,path,name,name_last,created_at)

VALUES (row.id,row.path,row.name,row.name_last,row.created_at);



END LOOP;


END


$$ language 'plpgsql';

[GENERAL] pgAdmin4 - no Query tools available

[elliot_rock]

Mac OSX,

No query tools on right click on database or via the tool menu.

Possible issues:

I installed postgresql-9.6.3-1-osx.dmg after I installed pgAdmin in order to
setup a localhosted db.

Previous to this I was using pgAdmin4 on a remote server to an acceptable
standard.

Thanks




--
View this message in context: 
http://www.postgresql-archive.org/pgAdmin4-no-Query-tools-available-tp5963635.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] SELECT statement with sub-queries

[Adrian Klaver]

On 05/28/2017 11:54 AM, Michelle Konzack wrote:

On 2017-05-28 11:23:47 Adrian Klaver hacked into the keyboard:

On 05/28/2017 10:53 AM, Michelle Konzack wrote:


SELECT * FROM products WHERE category IN
 (SELECT categories.cat FROM categories WHERE
 categories.serial = products.category);



Because you are comparing categories.cat


ehm no


Actually yes:

SELECT categories.cat FROM categories WHERE
categories.serial = products.category

is going to select categories.cat which is a varchar.

SELECT * FROM products WHERE category IN ...

is asking to select all fields from where the products.category field is 
in the output of the above sub-select, which reduces down to 
products.category = categories.cat

or
integer = varchar. As the error message says , that is not possible.



I want to replace in the output the numerical ID from "products.category"
with the value of "categories.cat", where the "products.category" match
the "categories.serial"



which is a varchar to
products.category which is an integer.  The above is crying out for
FOREIGN KEYS. For the time being I going to assume products.category
is a faux FK to categories.serial so;

SELECT * FROM products WHERE products.category = categories.serial;


My mistake, it should be:

SELECT categories.cat, manufacturer, p_name, p_images, p_desc
FROM products, categories WHERE products.category = categories.serial;



This is not working





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] SELECT statement with sub-queries

[David G. Johnston]

On Sun, May 28, 2017 at 11:54 AM, Michelle Konzack  wrote:

>
> I want to replace in the output the numerical ID from "products.category"
> with the value of "categories.cat", where the "products.category" match
> the "categories.serial"
>

​Then go and read the first response in this thread, ​Amitabh Kant's, and
confirm it works or say why it does not.  Personally I prefer "products
JOIN categories ON/USING" but that is style and the "FROM products,
categories WHERE" formulation will give the correct answer.

The syntax error in your original message is, like others have said,
because "==(int, int)" is not a known operator.

And typically one writes:  "EXISTS (correlated subquery)" instead of "IN
(correlated subquery)".  But if you want to replace a column in the output
a predicate subquery (WHERE clause) that effectively implements a semi-join
(only return results from one table) will not help you since you cannot
actually refer to any of the columns in the subquery in the main query.
You need an actual join to do that.  IOW, writing "FROM products WHERE"
when the output value you want is on the category table isn't going to help
you.

David J.

Re: [GENERAL] Help with terminology to describe what my software does please?

[Neil Anderson]

>> Cluster comparison would only occur if you have two or more clusters on
>> the same server, although it's possible to compare across servers,
>
>
> Explain, because as I understand it a server = one cluster:
>

I think he was using server in the server=one machine sense, ie a
single machine/server can have multiple clusters/database servers.

> https://www.postgresql.org/docs/9.6/static/app-pg-ctl.html
>
> "The init or initdb mode creates a new PostgreSQL database cluster. A
> database cluster is a collection of databases that are managed by a single
> server instance. This mode invokes the initdb command. See initdb for
> details."
>
>> but that would involve a lot more work. AFAIK, the only differences for a
>> cluster would be:
>> 1. PostgreSQL version
>> 2. path to database
>> 3. database users (note: it is also possible to make users database
>> specific)
>> 4. list of defined databases
>
>
> And anything different below the above, I am thinking checking a dev cluster
> against a production cluster.
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Help with terminology to describe what my software does please?

[Neil Anderson]

>
>
> Cluster comparison would only occur if you have two or more clusters on
> the same server, although it's possible to compare across servers,
> but that would involve a lot more work. AFAIK, the only differences for a
> cluster would be:
> 1. PostgreSQL version
> 2. path to database
> 3. database users (note: it is also possible to make users database
> specific)
> 4. list of defined databases
>

I was considering configuration settings to be at the cluster level too.
Stuff from pg_settings or pg_config. Also I think tablespaces are at that
level too. What do you think?


> Database comparison would involve db names, owners, encodings, tablespaces
> and acl's
> You might also want to include sizes. You can use the following two
> queries to help
> with that
>
> SELECT db.datname,
>au.rolname as datdba,
>pg_encoding_to_char(db.encoding) as encoding,
>db.datallowconn,
>db.datconnlimit,
>db.datfrozenxid,
>tb.spcname as tblspc,
>db.datacl
>   FROM pg_database db
>   JOIN pg_authid au ON au.oid = db.datdba
>   JOIN pg_tablespace tb ON tb.oid = db.dattablespace
>  ORDER BY 1;
>
> SELECT datname,
>pg_size_pretty(pg_database_size(datname))as size_pretty,
>pg_database_size(datname) as size,
>(SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
>   FROM pg_database)  AS total,
>((pg_database_size(datname) / (SELECT SUM(
> pg_database_size(datname))
>FROM pg_database) ) *
> 100)::numeric(6,3) AS pct
>   FROM pg_database
>   ORDER BY datname;
>

That's a great idea! Thanks for the info.


>
>

>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>



-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.com

Re: [GENERAL] SELECT statement with sub-queries

[Michelle Konzack]

On 2017-05-28 20:19:59 m...@ft-c.de hacked into the keyboard:
> Hallo,
> 
> SELECT *
> FROM products
> WHERE exists
>  (SELECT categories.cat FROM categories WHERE
>  categories.serial==products.category);

This does not give an error but does nothing

> or
> SELECT * FROM products
> WHERE category IN
>   (SELECT categories.cat FROM categories);

This give an error

See previously mail for what I want to archive


-- 
Michelle KonzackMiila ITSystems @ TDnet
GNU/Linux Developer 00372-54541400


signature.asc
Description: Digital signature

Re: [GENERAL] SELECT statement with sub-queries

[Michelle Konzack]

On 2017-05-28 11:23:47 Adrian Klaver hacked into the keyboard:
> On 05/28/2017 10:53 AM, Michelle Konzack wrote:
> >>
> >>SELECT * FROM products WHERE category IN
> >> (SELECT categories.cat FROM categories WHERE
> >> categories.serial = products.category);

> Because you are comparing categories.cat

ehm no

I want to replace in the output the numerical ID from "products.category"
with the value of "categories.cat", where the "products.category" match
the "categories.serial"


> which is a varchar to
> products.category which is an integer.  The above is crying out for
> FOREIGN KEYS. For the time being I going to assume products.category
> is a faux FK to categories.serial so;
> 
> SELECT * FROM products WHERE products.category = categories.serial;

This is not working


-- 
Michelle KonzackMiila ITSystems @ TDnet
GNU/Linux Developer 00372-54541400


signature.asc
Description: Digital signature

Re: [GENERAL] SELECT statement with sub-queries

[ml]

On 28.05.2017 20:19, m...@ft-c.de wrote:

Hallo,

SELECT *
FROM products
WHERE exists
  (SELECT categories.cat FROM categories WHERE
  categories.serial==products.category);

   categories.serial = products.category);  -- one equal sign


or
SELECT * FROM products
WHERE category IN
   (SELECT categories.cat FROM categories);

On 28.05.2017 19:03, Michelle Konzack wrote:

Hello *,

I try to get columns from my database with a singel SELECT, but I stuck.

I have 3 tables like:


1)  categories (serial,cat)

2)  manufacturers (serial,m_name)

3)  products (serial,category,manufacturer,p_name)


where the "category" and "manufacturer"
are numerical IDs from the two tables above.

So I like to replace the numerical IDs with the appropriated values,
mean

SELECT * FROM products WHERE category IN
 (SELECT categories.cat FROM categories WHERE
 categories.serial==products.category);

But I get:

ERROR:  operator does not exist: integer==integer
LINE1: ...gories.cat FROM categories WHERE  
categories.serial==products.category);

  ^
Hmm, "serial" is an "integer" and the "category" and "manufacturer" too.

So whats wrong with it?













--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] SELECT statement with sub-queries

[Adrian Klaver]

On 05/28/2017 10:53 AM, Michelle Konzack wrote:

On 2017-05-29 03:24:54 rob stone hacked into the keyboard:

You only need a single equals sign in SQL.

SELECT * FROM products WHERE category IN
 (SELECT categories.cat FROM categories WHERE
 categories.serial = products.category);


I have tried this too, but then I get:

ERROR:  operator does not exist: integer = character varying
LINE 1: SELECT * FROM products WHERE category IN (SELECT categories
   ^

My sql file is:

8<--
DROP TABLE categories;
DROP TABLE manufacturers;
DROP TABLE products;

CREATE TABLE categories (
serial  integer NOT NULL,
cat varchar(40),
);

CREATE TABLE manufacturers (
serial  integer NOT NULL,
m_name  varchar(40),
m_address   varchar(200),
m_imagesvarchar(100),
m_desc  varchar(1000),
);

CREATE TABLE products (
serial  integer NOT NULL,
categoryinteger NOT NULL,
manufacturerinteger NOT NULL,
p_name  varchar(40),
p_imagesvarchar(100),
p_desc  varchar(1),
);
8<--

This is WHY I am puzzeling arround with the "integer" error.


Because you are comparing categories.cat which is a varchar to 
products.category which is an integer.  The above is crying out for 
FOREIGN KEYS. For the time being I going to assume products.category is 
a faux FK to categories.serial so;


SELECT * FROM products WHERE products.category = categories.serial;








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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] SELECT statement with sub-queries

[ml]

Hallo,

SELECT *
FROM products
WHERE exists
 (SELECT categories.cat FROM categories WHERE
 categories.serial==products.category);
or
SELECT * FROM products
WHERE category IN
  (SELECT categories.cat FROM categories);

On 28.05.2017 19:03, Michelle Konzack wrote:

Hello *,

I try to get columns from my database with a singel SELECT, but I stuck.

I have 3 tables like:


1)  categories (serial,cat)

2)  manufacturers (serial,m_name)

3)  products (serial,category,manufacturer,p_name)


where the "category" and "manufacturer"
are numerical IDs from the two tables above.

So I like to replace the numerical IDs with the appropriated values,
mean

SELECT * FROM products WHERE category IN
 (SELECT categories.cat FROM categories WHERE
 categories.serial==products.category);

But I get:

ERROR:  operator does not exist: integer==integer
LINE1: ...gories.cat FROM categories WHERE  
categories.serial==products.category);
  ^
Hmm, "serial" is an "integer" and the "category" and "manufacturer" too.

So whats wrong with it?










--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] SELECT statement with sub-queries

[Michelle Konzack]

On 2017-05-29 03:24:54 rob stone hacked into the keyboard:
> You only need a single equals sign in SQL.
> 
> SELECT * FROM products WHERE category IN 
>     (SELECT categories.cat FROM categories WHERE 
>     categories.serial = products.category);

I have tried this too, but then I get:

ERROR:  operator does not exist: integer = character varying
LINE 1: SELECT * FROM products WHERE category IN (SELECT categories
  ^

My sql file is:

8<--
DROP TABLE categories;
DROP TABLE manufacturers;
DROP TABLE products;

CREATE TABLE categories (
serial  integer NOT NULL,
cat varchar(40),
);

CREATE TABLE manufacturers (
serial  integer NOT NULL,
m_name  varchar(40),
m_address   varchar(200),
m_imagesvarchar(100),
m_desc  varchar(1000),
);

CREATE TABLE products (
serial  integer NOT NULL,
categoryinteger NOT NULL,
manufacturerinteger NOT NULL,
p_name  varchar(40),
p_imagesvarchar(100),
p_desc  varchar(1),
);
8<--

This is WHY I am puzzeling arround with the "integer" error.


-- 
Michelle KonzackMiila ITSystems @ TDnet
GNU/Linux Developer 00372-54541400


signature.asc
Description: Digital signature

Re: [GENERAL] SELECT statement with sub-queries

[rob stone]

Hello,

On Sun, 2017-05-28 at 20:03 +0300, Michelle Konzack wrote:
> Hello *,
> 
> I try to get columns from my database with a singel SELECT, but I
> stuck.
> 
> I have 3 tables like:
> 
> 
> 1)  categories (serial,cat)
> 
> 2)  manufacturers (serial,m_name)
> 
> 3)  products (serial,category,manufacturer,p_name)
> 
> 
> where the "category" and "manufacturer"
> are numerical IDs from the two tables above.
> 
> So I like to replace the numerical IDs with the appropriated values,
> mean
> 
> SELECT * FROM products WHERE category IN 
> (SELECT categories.cat FROM categories WHERE 
> categories.serial==products.category);
> 
> But I get:
> 
> ERROR:  operator does not exist: integer==integer
> LINE1: ...gories.cat FROM categories
> WHERE  categories.serial==products.category);
>  ^
> Hmm, "serial" is an "integer" and the "category" and "manufacturer"
> too.
> 
> So whats wrong with it?
> 
> 

You only need a single equals sign in SQL.

SELECT * FROM products WHERE category IN 
    (SELECT categories.cat FROM categories WHERE 
    categories.serial = products.category);

Cheers,
Rob


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] SELECT statement with sub-queries

[Amitabh Kant]

On Sun, May 28, 2017 at 10:33 PM, Michelle Konzack  wrote:

> Hello *,
>
> I try to get columns from my database with a singel SELECT, but I stuck.
>
> I have 3 tables like:
>
>
> 1)  categories (serial,cat)
>
> 2)  manufacturers (serial,m_name)
>
> 3)  products (serial,category,manufacturer,p_name)
>
>
> where the "category" and "manufacturer"
> are numerical IDs from the two tables above.
>
> So I like to replace the numerical IDs with the appropriated values,
> mean
>
> SELECT * FROM products WHERE category IN
> (SELECT categories.cat FROM categories WHERE
> categories.serial==products.category);
>
> But I get:
>
> ERROR:  operator does not exist: integer==integer
> LINE1: ...gories.cat FROM categories WHERE  categories.serial==products.
> category);
>  ^
> Hmm, "serial" is an "integer" and the "category" and "manufacturer" too.
>
> So whats wrong with it?
>
>
>
>
>
>
>
> --
> Michelle KonzackMiila ITSystems @ TDnet
> GNU/Linux Developer 00372-54541400
>

Wouldn't a simple join like the one below suffice:

Select a.*,b.cat from products as a, categories as b where
a.category=b.serial

or am I missing something?

Re: [GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Thomas Kellerer]

Adrian Klaver schrieb am 28.05.2017 um 17:51:

After I finally found the EnterpriseDB Beta binaries (they are pretty well 
hidden) I tested with those, and everything works fine there.


For those following along, where would that be?


Here they are:

https://www.enterprisedb.com/products-services-training/pgdevdownload

Note that the "Binaries Version" (the ZIP download) is missing several .exe 
program (most importantly psql.exe and pg_dump.exe but some others as well).

The files in the installer package are complete.





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] SELECT statement with sub-queries

[Michelle Konzack]

Hello *,

I try to get columns from my database with a singel SELECT, but I stuck.

I have 3 tables like:


1)  categories (serial,cat)

2)  manufacturers (serial,m_name)

3)  products (serial,category,manufacturer,p_name)


where the "category" and "manufacturer"
are numerical IDs from the two tables above.

So I like to replace the numerical IDs with the appropriated values,
mean

SELECT * FROM products WHERE category IN 
(SELECT categories.cat FROM categories WHERE 
categories.serial==products.category);

But I get:

ERROR:  operator does not exist: integer==integer
LINE1: ...gories.cat FROM categories WHERE  
categories.serial==products.category);
 ^
Hmm, "serial" is an "integer" and the "category" and "manufacturer" too.

So whats wrong with it?







-- 
Michelle KonzackMiila ITSystems @ TDnet
GNU/Linux Developer 00372-54541400


signature.asc
Description: Digital signature

Re: [GENERAL] Question regarding the output of postgresql *explain* command

[Arup Rakshit]

Hi Tom,

Thanks, I’ll read this page.




> On May 28, 2017, at 8:36 PM, Tom Lane  wrote:
> 
> Arup Rakshit  writes:
>> I was reading to day how indexing works. And I was trying some query, for 
>> example below one. What the range basically means (cost=0.28..8.30 ? I don’t 
>> understand this.
> 
> https://www.postgresql.org/docs/current/static/using-explain.html
> 
>   regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Tom Lane]

Thomas Kellerer  writes:
> Tom Lane schrieb am 26.05.2017 um 20:18:
>>> The error message reported in the logfile is:
>>> pg_dump: unrecognized collation provider: p

>> Ugh :-( ... seems like a rather obvious typo in dumpCollation().
>> Thanks for finding it!

> When I drop that collation from the source database, pg_upgrade works without 
> problems with the EnterprsieDB binaries.

I committed a fix for that, will be in 10beta2.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=764cb2b596ced6aea4d83fd52ff628bdedb63316

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Adrian Klaver]

On 05/28/2017 08:41 AM, Thomas Kellerer wrote:

Tom Lane schrieb am 26.05.2017 um 20:18:




Apparently BigSQL forgot to include contrib/xml2 in their distribution;
you should ping them about that one.


I can confirm that it's a BigSQL problem.
After I finally found the EnterpriseDB Beta binaries (they are pretty 
well hidden) I tested with those, and everything works fine there.


For those following along, where would that be?




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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Thomas Kellerer]

Tom Lane schrieb am 26.05.2017 um 20:18:

I just tried pg_upgrade from the Postgres 10 Beta1 (from the BigSQL 
distribution) to upgrade a 9.6 cluster.
pg_upgrade --check fails with the following messages:



could not load library "$libdir/pgxml":
ERROR:  could not load library 
"d:/etc/postgres-10/pgsql/lib/postgresql/pgxml.dll": unknown error 126


Apparently BigSQL forgot to include contrib/xml2 in their distribution;
you should ping them about that one.


I can confirm that it's a BigSQL problem.
After I finally found the EnterpriseDB Beta binaries (they are pretty well 
hidden) I tested with those, and everything works fine there.
 

When I then run pg_upgrade without the --check option, it fails when taking the 
schema only dump from one database.
The error message reported in the logfile is:
pg_dump: unrecognized collation provider: p


Ugh :-( ... seems like a rather obvious typo in dumpCollation().
Thanks for finding it!


When I drop that collation from the source database, pg_upgrade works without 
problems with the EnterprsieDB binaries.





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Help with terminology to describe what my software does please?

[Adrian Klaver]

On 05/28/2017 07:53 AM, Melvin Davidson wrote:











Cluster comparison would only occur if you have two or more clusters on 
the same server, although it's possible to compare across servers,


Explain, because as I understand it a server = one cluster:

https://www.postgresql.org/docs/9.6/static/app-pg-ctl.html

"The init or initdb mode creates a new PostgreSQL database cluster. A 
database cluster is a collection of databases that are managed by a 
single server instance. This mode invokes the initdb command. See initdb 
for details."


but that would involve a lot more work. AFAIK, the only differences for 
a cluster would be:

1. PostgreSQL version
2. path to database
3. database users (note: it is also possible to make users database 
specific)

4. list of defined databases


And anything different below the above, I am thinking checking a dev 
cluster against a production cluster.




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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Help with terminology to describe what my software does please?

[Tom Lane]

Neil Anderson  writes:
> I guess I don't know what is the most common way to say that it
> compares everything but the data. Any suggestions from your
> experience?

FWIW, I think it's pretty common to use "schema" in an abstract way
to mean "the structure of your database", ie everything but the data.
(It's unfortunate that the SQL standard commandeered the word to
mean a database namespace; but it's not like there are no other words
with more than one meaning.)

So I don't see any big problem with calling your tool a schema comparator.
You could maybe make your docs a bit clearer if you consistently refer
to the namespace objects as "SQL schemas", reserving the generic term
for the generic meaning.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Question regarding the output of postgresql *explain* command

[Tom Lane]

Arup Rakshit  writes:
> I was reading to day how indexing works. And I was trying some query, for 
> example below one. What the range basically means (cost=0.28..8.30 ? I don’t 
> understand this.

https://www.postgresql.org/docs/current/static/using-explain.html

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Help with terminology to describe what my software does please?

[Melvin Davidson]

On Sun, May 28, 2017 at 9:51 AM, Adrian Klaver 
wrote:

> On 05/28/2017 05:49 AM, Neil Anderson wrote:
>
>> Hi,
>>
>> I'm working on a tool that can compare the properties of Postgres
>> objects from different instances, finding the differences and
>> outputting the update SQL.
>>
>> It can compare objects that are defined at the cluster, database or
>> schema level. As such I'm finding it difficult to describe what the
>> tool does simply and accurately. I've tried 'compares PostgreSQL
>> schemas' but that doesn't capture the database and cluster parts,
>> 'compares PostgreSQL schema and database objects'. That sort of thing.
>> Right now I have a mix of terms on my website and I would prefer to
>> tighten it up.
>>
>> I guess I don't know what is the most common way to say that it
>> compares everything but the data. Any suggestions from your
>> experience?
>>
>
> From above the first sentence of the second paragraph seems to me the best
> description of what you are doing.
>
>
>> Thanks,
>> Neil
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


Cluster comparison would only occur if you have two or more clusters on the
same server, although it's possible to compare across servers,
but that would involve a lot more work. AFAIK, the only differences for a
cluster would be:
1. PostgreSQL version
2. path to database
3. database users (note: it is also possible to make users database
specific)
4. list of defined databases

Database comparison would involve db names, owners, encodings, tablespaces
and acl's
You might also want to include sizes. You can use the following two queries
to help
with that

SELECT db.datname,
   au.rolname as datdba,
   pg_encoding_to_char(db.encoding) as encoding,
   db.datallowconn,
   db.datconnlimit,
   db.datfrozenxid,
   tb.spcname as tblspc,
   db.datacl
  FROM pg_database db
  JOIN pg_authid au ON au.oid = db.datdba
  JOIN pg_tablespace tb ON tb.oid = db.dattablespace
 ORDER BY 1;

SELECT datname,
   pg_size_pretty(pg_database_size(datname))as size_pretty,
   pg_database_size(datname) as size,
   (SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
  FROM pg_database)  AS total,
   ((pg_database_size(datname) / (SELECT SUM(
pg_database_size(datname))
   FROM pg_database) ) *
100)::numeric(6,3) AS pct
  FROM pg_database
  ORDER BY datname;

 schema comparison is a lot more complication as it involves comparing
 collations
 domains
 functions
 trigger functions
 sequences
 tables
 types
 views

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Question regarding the output of postgresql *explain* command

[Arup Rakshit]

Hi,


I was reading to day how indexing works. And I was trying some query, for 
example below one. What the range basically means (cost=0.28..8.30 ? I don’t 
understand this.


———


arup@ror ~/part-time-projects/entrylvl (add_index_to_job_sources)$ rails db
psql (9.5.0)
Type "help" for help.

entrylvl_dev=# \d job_sources;
   Table "public.job_sources"
Column |Type |Modifiers
---+-+--
 id| bigint  | not null default 
nextval('job_sources_id_seq'::regclass)
 job_id| bigint  |
 source| character varying   |
 job_source_id | character varying   |
 created_at| timestamp without time zone | not null
 updated_at| timestamp without time zone | not null
Indexes:
"job_sources_pkey" PRIMARY KEY, btree (id)
"index_job_sources_on_source_and_job_source_id" UNIQUE, btree (source, 
job_source_id)
"index_job_sources_on_job_id" btree (job_id)
Foreign-key constraints:
"fk_rails_f45da00eca" FOREIGN KEY (job_id) REFERENCES jobs(id)

entrylvl_dev=# explain select * from job_sources where job_source_id = 
'p_1208b146c9eb8905' AND source = 'indeed';
QUERY PLAN
--
 Index Scan using index_job_sources_on_source_and_job_source_id on job_sources  
(cost=0.28..8.30 rows=1 width=58)
   Index Cond: (((source)::text = 'indeed'::text) AND ((job_source_id)::text = 
'p_1208b146c9eb8905'::text))
(2 rows)

entrylvl_dev=#

—


Thanks,
Arup

Re: [GENERAL] Help with terminology to describe what my software does please?

[Adrian Klaver]

On 05/28/2017 05:49 AM, Neil Anderson wrote:

Hi,

I'm working on a tool that can compare the properties of Postgres
objects from different instances, finding the differences and
outputting the update SQL.

It can compare objects that are defined at the cluster, database or
schema level. As such I'm finding it difficult to describe what the
tool does simply and accurately. I've tried 'compares PostgreSQL
schemas' but that doesn't capture the database and cluster parts,
'compares PostgreSQL schema and database objects'. That sort of thing.
Right now I have a mix of terms on my website and I would prefer to
tighten it up.

I guess I don't know what is the most common way to say that it
compares everything but the data. Any suggestions from your
experience?


From above the first sentence of the second paragraph seems to me the 
best description of what you are doing.




Thanks,
Neil





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] wal_retrieve_retry_interval

[Ludovic Vaugeois-Pepin]

On Sun, May 28, 2017 at 9:49 AM, Ludovic Vaugeois-Pepin
 wrote:
>
>
> On Sun, May 28, 2017 at 6:54 AM, Michael Paquier  
> wrote:
>>
>> On Sat, May 27, 2017 at 2:40 PM, Ludovic Vaugeois-Pepin
>>  wrote:
>> > Say, with 9.6.2,  a hot_standby fails to connect to a replication slot:
>> > FATAL:  could not start WAL streaming: ERROR:  replication slot "test3"
>> > does not exist
>> > or
>> > FATAL:  could not connect to the primary server: FATAL:  the database
>> > system is starting up
>> >
>> > Is there a way to reduce the time it takes until the next attempt? I
>> > assumed, wrongly I think, that this would be wal_retrieve_retry_interval,
>> > but it seems that it won't make a difference. I tried setting it to 3s, but
>> > it seems to take 15s still. Here are two log samples:
>>
>> Could you double-check your configuration? If I set
>> wal_retrieve_retry_interval to 1s on a standby, I am able to see a
>> connection attempt from a WAL receiver happening with this interval of
>> time in the case of repetitive failures.
>
>
> It really is set at 3s on all servers (master and standbies) earlier in the 
> "deployment" process at the same time "listen_addresses", "hot_standby", and 
> others are set. This doesn't seem to happen every time I run tests. I 
> increased logging to DEBUG1. This is what I got when the problem occurred 
> again. Note that the empty line is there in the log file.
>
> < 2017-05-28 09:29:36.127 CEST > LOG:  database system was shut down in 
> recovery at 2017-05-28 09:29:03 CEST
> < 2017-05-28 09:29:36.127 CEST > LOG:  entering standby mode
> < 2017-05-28 09:29:36.127 CEST > DEBUG:  checkpoint record is at 0/80002B8
> < 2017-05-28 09:29:36.128 CEST > DEBUG:  redo record is at 0/80002B8; 
> shutdown TRUE
> < 2017-05-28 09:29:36.128 CEST > DEBUG:  next transaction ID: 0:2535; next 
> OID: 18660
> < 2017-05-28 09:29:36.128 CEST > DEBUG:  next MultiXactId: 1; next 
> MultiXactOffset: 0
> < 2017-05-28 09:29:36.128 CEST > DEBUG:  oldest unfrozen transaction ID: 
> 1750, in database 1
> < 2017-05-28 09:29:36.128 CEST > DEBUG:  oldest MultiXactId: 1, in database 1
> < 2017-05-28 09:29:36.128 CEST > DEBUG:  commit timestamp Xid oldest/newest: 
> 0/0
> < 2017-05-28 09:29:36.128 CEST > DEBUG:  transaction ID wrap limit is 
> 2147485397, limited by database with OID 1
> < 2017-05-28 09:29:36.128 CEST > DEBUG:  MultiXactId wrap limit is 
> 2147483648, limited by database with OID 1
> < 2017-05-28 09:29:36.128 CEST > DEBUG:  starting up replication slots
> < 2017-05-28 09:29:36.128 CEST > DEBUG:  resetting unlogged relations: 
> cleanup 1 init 0
> < 2017-05-28 09:29:36.129 CEST > DEBUG:  initializing for hot standby
> < 2017-05-28 09:29:36.129 CEST > DEBUG:  recovery snapshots are now enabled
> < 2017-05-28 09:29:36.129 CEST > LOG:  consistent recovery state reached at 
> 0/8000328
> < 2017-05-28 09:29:36.129 CEST > LOG:  invalid record length at 0/8000328: 
> wanted 24, got 0
> < 2017-05-28 09:29:36.129 CEST > LOG:  database system is ready to accept 
> read only connections
> < 2017-05-28 09:29:36.135 CEST > FATAL:  could not connect to the primary 
> server: FATAL:  the database system is starting up
>
> < 2017-05-28 09:29:36.135 CEST > DEBUG:  invalid record length at 0/8000328: 
> wanted 24, got 0
> < 2017-05-28 09:29:51.153 CEST > DEBUG:  invalid record length at 0/8000328: 
> wanted 24, got 0
> < 2017-05-28 09:29:51.158 CEST > LOG:  fetching timeline history file for 
> timeline 4 from primary server
> < 2017-05-28 09:29:51.160 CEST > LOG:  started streaming WAL from primary at 
> 0/800 on timeline 3
> < 2017-05-28 09:29:51.160 CEST > LOG:  replication terminated by primary 
> server
> < 2017-05-28 09:29:51.160 CEST > DETAIL:  End of WAL reached on timeline 3 at 
> 0/8000328.
> < 2017-05-28 09:29:51.161 CEST > DEBUG:  walreceiver ended streaming and 
> awaits new instructions
> < 2017-05-28 09:29:51.161 CEST > LOG:  new target timeline is 4
> < 2017-05-28 09:29:51.161 CEST > DEBUG:  invalid record length at 0/8000328: 
> wanted 24, got 0
> < 2017-05-28 09:29:51.161 CEST > LOG:  restarted WAL streaming at 0/800 
> on timeline 4
> < 2017-05-28 09:29:51.191 CEST > LOG:  redo starts at 0/8000328


And this is the log with log_min_messages DEBUG5:


< 2017-05-28 14:48:10.108 CEST > LOG:  invalid record length at
0/8000398: wanted 24, got 0
< 2017-05-28 14:48:10.108 CEST > DEBUG:  switched WAL source from
archive to stream after failure
< 2017-05-28 14:48:10.110 CEST > DEBUG:  checkpointer updated shared
memory configuration values
< 2017-05-28 14:48:10.110 CEST > LOG:  database system is ready to
accept read only connections
< 2017-05-28 14:48:10.112 CEST > DEBUG:  find_in_dynamic_libpath:
trying "/usr/pgsql-9.6/lib/libpqwalreceiver"
< 2017-05-28 14:48:10.112 CEST > DEBUG:  find_in_dynamic_libpath:
trying "/usr/pgsql-9.6/lib/libpqwalreceiver.so"
< 2017-05-28 14:48:10.126 CEST > FATAL:  could not connect to the
primary 

[GENERAL] Help with terminology to describe what my software does please?

[Neil Anderson]

Hi,

I'm working on a tool that can compare the properties of Postgres
objects from different instances, finding the differences and
outputting the update SQL.

It can compare objects that are defined at the cluster, database or
schema level. As such I'm finding it difficult to describe what the
tool does simply and accurately. I've tried 'compares PostgreSQL
schemas' but that doesn't capture the database and cluster parts,
'compares PostgreSQL schema and database objects'. That sort of thing.
Right now I have a mix of terms on my website and I would prefer to
tighten it up.

I guess I don't know what is the most common way to say that it
compares everything but the data. Any suggestions from your
experience?

Thanks,
Neil


-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Fwd: [GENERAL] wal_retrieve_retry_interval

[Ludovic Vaugeois-Pepin]

On Sun, May 28, 2017 at 6:54 AM, Michael Paquier 
wrote:

> On Sat, May 27, 2017 at 2:40 PM, Ludovic Vaugeois-Pepin
>  wrote:
> > Say, with 9.6.2,  a hot_standby fails to connect to a replication slot:
> > FATAL:  could not start WAL streaming: ERROR:  replication slot
> "test3"
> > does not exist
> > or
> > FATAL:  could not connect to the primary server: FATAL:  the database
> > system is starting up
> >
> > Is there a way to reduce the time it takes until the next attempt? I
> > assumed, wrongly I think, that this would be wal_retrieve_retry_interval,
> > but it seems that it won't make a difference. I tried setting it to 3s,
> but
> > it seems to take 15s still. Here are two log samples:
>
> Could you double-check your configuration? If I set
> wal_retrieve_retry_interval to 1s on a standby, I am able to see a
> connection attempt from a WAL receiver happening with this interval of
> time in the case of repetitive failures.
>

It really is set at 3s on all servers (master and standbies) earlier in the
"deployment" process at the same time "listen_addresses", "hot_standby",
and others are set. This doesn't seem to happen every time I run tests. I
increased logging to DEBUG1. This is what I got when the problem occurred
again. Note that the empty line is there in the log file.

< 2017-05-28 09:29:36.127 CEST > LOG:  database system was shut down in
recovery at 2017-05-28 09:29:03 CEST
< 2017-05-28 09:29:36.127 CEST > LOG:  entering standby mode
< 2017-05-28 09:29:36.127 CEST > DEBUG:  checkpoint record is at 0/80002B8
< 2017-05-28 09:29:36.128 CEST > DEBUG:  redo record is at 0/80002B8;
shutdown TRUE
< 2017-05-28 09:29:36.128 CEST > DEBUG:  next transaction ID: 0:2535; next
OID: 18660
< 2017-05-28 09:29:36.128 CEST > DEBUG:  next MultiXactId: 1; next
MultiXactOffset: 0
< 2017-05-28 09:29:36.128 CEST > DEBUG:  oldest unfrozen transaction ID:
1750, in database 1
< 2017-05-28 09:29:36.128 CEST > DEBUG:  oldest MultiXactId: 1, in database
1
< 2017-05-28 09:29:36.128 CEST > DEBUG:  commit timestamp Xid
oldest/newest: 0/0
< 2017-05-28 09:29:36.128 CEST > DEBUG:  transaction ID wrap limit is
2147485397 <(214)%20748-5397>, limited by database with OID 1
< 2017-05-28 09:29:36.128 CEST > DEBUG:  MultiXactId wrap limit is
2147483648 <(214)%20748-3648>, limited by database with OID 1
< 2017-05-28 09:29:36.128 CEST > DEBUG:  starting up replication slots
< 2017-05-28 09:29:36.128 CEST > DEBUG:  resetting unlogged relations:
cleanup 1 init 0
< 2017-05-28 09:29:36.129 CEST > DEBUG:  initializing for hot standby
< 2017-05-28 09:29:36.129 CEST > DEBUG:  recovery snapshots are now enabled
< 2017-05-28 09:29:36.129 CEST > LOG:  consistent recovery state reached at
0/8000328
< 2017-05-28 09:29:36.129 CEST > LOG:  invalid record length at 0/8000328:
wanted 24, got 0
< 2017-05-28 09:29:36.129 CEST > LOG:  database system is ready to accept
read only connections
< 2017-05-28 09:29:36.135 CEST > FATAL:  could not connect to the primary
server: FATAL:  the database system is starting up

< 2017-05-28 09:29:36.135 CEST > DEBUG:  invalid record length at
0/8000328: wanted 24, got 0
< 2017-05-28 09:29:51.153 CEST > DEBUG:  invalid record length at
0/8000328: wanted 24, got 0
< 2017-05-28 09:29:51.158 CEST > LOG:  fetching timeline history file for
timeline 4 from primary server
< 2017-05-28 09:29:51.160 CEST > LOG:  started streaming WAL from primary
at 0/800 on timeline 3
< 2017-05-28 09:29:51.160 CEST > LOG:  replication terminated by primary
server
< 2017-05-28 09:29:51.160 CEST > DETAIL:  End of WAL reached on timeline 3
at 0/8000328.
< 2017-05-28 09:29:51.161 CEST > DEBUG:  walreceiver ended streaming and
awaits new instructions
< 2017-05-28 09:29:51.161 CEST > LOG:  new target timeline is 4
< 2017-05-28 09:29:51.161 CEST > DEBUG:  invalid record length at
0/8000328: wanted 24, got 0
< 2017-05-28 09:29:51.161 CEST > LOG:  restarted WAL streaming at 0/800
on timeline 4
< 2017-05-28 09:29:51.191 CEST > LOG:  redo starts at 0/8000328




> --
> Michael
>



-- 
Ludovic Vaugeois-Pepin



-- 
Ludovic Vaugeois-Pepin