Re: heads up on large text fields.
> 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: New behavior with JDBC 42.2.5
On 9/21/18 5:46 PM, Ravi Krishna wrote:
First are doing a socket connection or a host connection? Socket
connections ignore sslmode.
The URL template of JDBC used by dbeaver is
jdbc:postgresql://{host}[:{port}]/[{database}]
From the manual
"The host component is interpreted as described for the parameter host. In
particular, a Unix-domain socket connection is chosen if the host part is either empty or
starts with a slash, otherwise a TCP/IP connection is initiated."
In our case the host name does not start with a slash. So I conclude it is
using TCP/IP.
psql postgresql://localhost:5432/test?sslmode=require
psql postgresql://localhost:5432/test?sslmode=disable
We are using dbeaver, not psql. dbeaver also provides disable option for sslmode. I set
sslmode=disable and it immediately rejected connection with error "SSL Mode
off".
So the puzzle is -> in version 42.1.4 when we do not check "Use SSL " button,
connection
fails with "SSL Mode off". In that version we check the box "Use SSL" and then
in the dropdown
for sslmode, we select sslmode=require. Only then the connection passes.
In version 42.2.5 even if we do not check "Use SSL" it still behaves the same as
"Use SSL" checked and sslmode=require.
I was not able to import the security bulletin fully, but am I right in
concluding
that JDBC 42.2.5 always turns on sslmode=require unless we explicitly set
sslmode=disable.
Looks that way:
https://github.com/pgjdbc/pgjdbc/commit/cdeeaca47dc3bc6f727c79a582c9e4123099526e
Line 181-184
* Parameter governing the use of SSL. The allowed values are {@code
disable}, {@code allow},
* {@code prefer}, {@code require}, {@code verify-ca}, {@code
verify-full}.
* If {@code ssl} property is empty or set to {@code true} it implies
{@code verify-full}.
* Default mode is "require"
Thanks for your help.
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: heads up on large text fields.
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
Re: New behavior with JDBC 42.2.5
> First are doing a socket connection or a host connection? Socket
> connections ignore sslmode.
The URL template of JDBC used by dbeaver is
jdbc:postgresql://{host}[:{port}]/[{database}]
>From the manual
"The host component is interpreted as described for the parameter host. In
particular, a Unix-domain socket connection is chosen if the host part is
either empty or starts with a slash, otherwise a TCP/IP connection is
initiated."
In our case the host name does not start with a slash. So I conclude it is
using TCP/IP.
> psql postgresql://localhost:5432/test?sslmode=require
>
>
> psql postgresql://localhost:5432/test?sslmode=disable
We are using dbeaver, not psql. dbeaver also provides disable option for
sslmode. I set sslmode=disable and it immediately rejected connection with
error "SSL Mode off".
So the puzzle is -> in version 42.1.4 when we do not check "Use SSL " button,
connection
fails with "SSL Mode off". In that version we check the box "Use SSL" and then
in the dropdown
for sslmode, we select sslmode=require. Only then the connection passes.
In version 42.2.5 even if we do not check "Use SSL" it still behaves the same
as
"Use SSL" checked and sslmode=require.
I was not able to import the security bulletin fully, but am I right in
concluding
that JDBC 42.2.5 always turns on sslmode=require unless we explicitly set
sslmode=disable.
Thanks for your help.
heads up on large text fields.
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: New behavior with JDBC 42.2.5
On 9/21/18 10:12 AM, Ravi Krishna wrote: We recently upgraded our JDBC driver to 42.2.5 after seeing this https://www.postgresql.org/about/news/1883/ All of our PG databases mandates SSL connections. So the first line in pg_hba.conf is hostnossl all all all reject We use dbeaver and while setting up connection we check box SSL (require). Attempt to connect without that checked would result in the following error "pg_hba.conf rejected the connection: SSL off" With the JDBC version listed in the subject, we are finding that we can connect to the database even without checking SSL. Is that expected with this version of JDBC. Does it automatically make the connection an SSL one? Otherwise it is hard to see PG allowing that connection to pass. First are doing a socket connection or a host connection? Socket connections ignore sslmode. Second what happens if you do some version of this?: psql postgresql://localhost:5432/test?sslmode=require psql postgresql://localhost:5432/test?sslmode=disable thanks -- Adrian Klaver adrian.kla...@aklaver.com
Re: PostgreSQl, PHP and IIS
On 9/21/18 10:35 AM, Mark Williams wrote: I have PHP v7.2.7 and PostgreSQL v10 with Windows IIS. I am trying to get this to work following any advice I have been able to find on the web, but with no success. My configuration so far is as below. PHP.ini extension list includes both php_pdo_pgsql.dll and php_pgsql.dll. Both these dlls are located in the correct extensions directory according to phpinfo. According to phpinfo the Configuration File Path is "C:\Windows" and the Loaded Configuration File is "C:\Program Files\PHP\v7.2\php.ini". I have the same php.ini file at both locations. I have copied the version of libpq.dll from the lib folder of the postgresql installation to the root folder of the PHP installation. I have added the bin folder of the postgresql installation to the Windows system search path. Phpinfo shows that pdo_pgsql extension is loaded but not pgsql. I have also tried copying the dependent dlls (ssleay32.dll, libeay32.dll, libintl-8.dll, libiconv-2.dll) from the postgresql bin folder to the php root folder). Phpinfo show php_pdo_pgsql is enabled, but not php_pgsql.dll and an undefined function call error is displayed by php when I try and run any pg php functions. Can anyone please shed any light on what more I need to do? So what is the error you are getting? Or to put it another way, what is not happening? Regards, Mark -- Adrian Klaver adrian.kla...@aklaver.com
Share TLS connections with different databases.
I have a scenario where a database server need to maintain a huge number of small databases. So to keep TLS clients connected to this server is a pain because PG requires to have a unique connection for each database. For databases deployed in cloud (like AWS) it is even worse, because we cannot proxy the connections from the database server. So in order to keep the number of connections acceptable for my env I usually use a pgbouncer like solution to manage the connections and reduce the number of connections to the server. It would be great if PG protocol support reusabilty of TLS connections and enable clients to switch the database for an established connection. I'd like to hear about your experience. Have you seen such scenarios? How are you dealing with it? -- Carlo Pires
Re: *Regarding brin_index on required column of the table
> On 21 Sep 2018, at 17:49, Durgamahesh Manne wrote: > > Considering how hard you try to get rid of duplicates, I'm quite convinced that you're at least short a few join conditions. Getting rid of duplicates early has the added benefit of having to aggregate fewer rows, which should drastically improve the performance of this query. In addition, your approach of using both distinct and group by raises a red flag that you're fighting symptoms (most likely: duplicate results) without understanding their causes. I can't think of a single situation where both are justified in the same (sub-)query. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
PostgreSQl, PHP and IIS
I have PHP v7.2.7 and PostgreSQL v10 with Windows IIS. I am trying to get this to work following any advice I have been able to find on the web, but with no success. My configuration so far is as below. PHP.ini extension list includes both php_pdo_pgsql.dll and php_pgsql.dll. Both these dlls are located in the correct extensions directory according to phpinfo. According to phpinfo the Configuration File Path is "C:\Windows" and the Loaded Configuration File is "C:\Program Files\PHP\v7.2\php.ini". I have the same php.ini file at both locations. I have copied the version of libpq.dll from the lib folder of the postgresql installation to the root folder of the PHP installation. I have added the bin folder of the postgresql installation to the Windows system search path. Phpinfo shows that pdo_pgsql extension is loaded but not pgsql. I have also tried copying the dependent dlls (ssleay32.dll, libeay32.dll, libintl-8.dll, libiconv-2.dll) from the postgresql bin folder to the php root folder). Phpinfo show php_pdo_pgsql is enabled, but not php_pgsql.dll and an undefined function call error is displayed by php when I try and run any pg php functions. Can anyone please shed any light on what more I need to do? Regards, Mark
Re: New behavior with JDBC 42.2.5
Just to clarify: With JDBC 42.1.4 attempt to connect from dbeaver without SSL results in the following error: "pg_hba.conf rejected the connection: SSL off" This is what we expect. With 42.2.5, the connection succeeds without SSL.
New behavior with JDBC 42.2.5
We recently upgraded our JDBC driver to 42.2.5 after seeing this https://www.postgresql.org/about/news/1883/ All of our PG databases mandates SSL connections. So the first line in pg_hba.conf is hostnossl all all all reject We use dbeaver and while setting up connection we check box SSL (require). Attempt to connect without that checked would result in the following error "pg_hba.conf rejected the connection: SSL off" With the JDBC version listed in the subject, we are finding that we can connect to the database even without checking SSL. Is that expected with this version of JDBC. Does it automatically make the connection an SSL one? Otherwise it is hard to see PG allowing that connection to pass. thanks
Re: *Regarding brin_index on required column of the table
> i can see a lot of max(string-field) (for instance, LastName, > MiddleName, FirstName). > wild guess: completely broken design, but i don't know your application > and use-case for that. > again, as i said already, i think this is a case for an in-deep > consultation. My thoughts exactly. There is a UNION also. A mere look at the SQL indicates that it will be a miracle if this runs fast. Tuning such queries in a mailing list is difficult, the best we can do is to give suggestion.
Re: *Regarding brin_index on required column of the table
Am 21.09.2018 um 17:49 schrieb Durgamahesh Manne:
Please find below attached query plan file
query and plan still hard to read :-(
Query:
SELECT distinct
Max(v."vchSubmittersCode") as vchSubmittersCode
, Max(v."vchRecordType") as vchRecordType
, Max(v."vchSequenceNumber") as vchSequenceNumber
, v."vchContractNumber"
,"vchContractPartyRoleQualifier"
,"vchPartyRole"
,Max("vchPartyNatural_Non_NaturalEntity") as
vchPartyNatural_Non_NaturalEntity
, Max("vchPartyLastName") as vchPartyLastName
,Max("vchPartyFirstName") as vchPartyFirstName
,Max("vchPartyMiddleName") as vchPartyMiddleName
, Max("vchPartyPrefix") as vchPartyPrefix
,Max("vchPartySuffix") as vchPartySuffix
, NULL "vchContractEntityE_mailAddress"
, "vchPartyID"
, Max("vchPartyIDQualifier") as vchPartyIDQualifier
,Max("vchTrustRevocabilityIndicator") as vchTrustRevocabilityIndicator
,NULL "vchContractEntityPhoneNumber"
,NULL "vchContractEntityPhoneExtension"
,Max(v."vchFiller1") as vchFiller1
,Max(v."vchRejectCode") as vchRejectCode
, Max("vchContractEntityAddressLine1") as vchContractEntityAddressLine1
, Max("vchContractEntityAddressLine2") as vchContractEntityAddressLine2
, Max("vchContractEntityCity") as vchContractEntityCity
, Max("vchContractEntityState") as vchContractEntityState
, Max("vchContractEntityZip") as vchContractEntityZip
, Max("vchContractEntityAddressLine3") as vchContractEntityAddressLine3
, Max("vchContractEntityAddressLine4") as vchContractEntityAddressLine4
, Max("vchContractEntityAddressLine5") as vchContractEntityAddressLine5
,Max("vchPartyDateofBirth") as vchPartyDateofBirth
, Max("vchPartyAddressLine1") as vchPartyAddressLine1
, Max("vchContractStatus") as vchContractStatus
, string_agg(distinct trim(s."vchAgentTaxID"),',') as vchAgentTaxID
, "vchPartyRole"
,Max(v."vchAdvisorLabel") as vchAdvisorLabel
,v."vchFileName"
,Max("vchpartycity") as vchpartycity
,Max("vchpartystate") as vchpartystate
,Max("vchpartypostalcode") as vchpartypostalcode
,string_agg(distinct trim(s."vchAgentFirstName")||'
'||trim(s."vchAgentMiddleName")||' '||trim(s."vchAgentLastName"),',') as
"AgentName"
FROM
TABLE1 as v
join"DTCC".TABLE2 AS s on v."vchContractNumber" = s."vchContractNumber"
where v."bFetch" = false
GROUP BY
"vchPartyRole"
,v."vchFileName"
,"vchPartyID"
,"vchPartyRole"
,"vchContractPartyRoleQualifier"
, v."vchContractNumber"
UNION SELECT distinct
max(j."vchSubmittersCode") as vchSubmittersCode
,max(j."vchRecordType") as vchRecordType
,max(j."vchSequenceNumber") as vchSequenceNumber
, j."vchContractNumber"
, max("vchContractEntityTypeCode") as vchContractEntityTypeCode
,"vchContractEntityRole"
,max("vchContractEntityNatural_Non_NaturalNameIndicator") as
vchContractEntityNatural_Non_NaturalNameIndicator
,max("vchContractEntityLastName") as vchContractEntityLastName
, max("vchContractEntityFirstName") as vchContractEntityFirstName
, max("vchContractEntityMiddleName") as vchContractEntityMiddleName
, max("vchContractEntityPrefix") as vchContractEntityPrefix
, max("vchContractEntitySuffix") as vchContractEntitySuffix
, max("vchContractEntityE_mailAddress") as
vchContractEntityE_mailAddress
, "vchContractEntityPersonalIdentifier"
, max("vchContractEntityPersonalQualifier") as
vchContractEntityPersonalQualifier
, max("vchTrustRevocabilityIndicator") as vchTrustRevocabilityIndicator
, max("vchContractEntityPhoneNumber") as vchContractEntityPhoneNumber
, max("vchContractEntityPhoneExtension") as
vchContractEntityPhoneExtension
, max(j."vchFiller1") as vchFiller1
, max(j."vchRejectCode") as vchRejectCode
, max("vchcontractentityaddressline1") as vchcontractentityaddressline1
,max("vchcontractentityaddressline2") as vchcontractentityaddressline2
,max("vchcontractentitycity") as vchcontractentitycity
, max("vchcontractentitystate") as vchcontractentitystate
,max("vchcontractentityzip") as vchcontractentityzip
, max("vchcontractentityaddressline3") as vchcontractentityaddressline3
,max("vchcontractentityaddressline4") as vchcontractentityaddressline4
,max("vchcontractentityaddressline5") as vchcontractentityaddressline5
, NULL "vchPartyDateofBirth"
, NULL "vchPartyAddressLine1"
, NULL "vchContractStatus"
, string_agg(distinct trim(j."vchagenttaxid"),',') as vchagenttaxid
, "vchContractEntityRole"
, max(j."vchAdvisorLabel") as vchAdvisorLabel
,j."vchFileName"
,NULL "vchpartycity"
, NULL "vchpartystate"
,NULL "vchpartypostalcode"
, trim(max(k."vchAgentFirstName"))||'
'||trim(max(k."vchAgentMiddleName"))||'
'||trim(max(k."vchAgentLastName")) as "AgentName"
FROM TABLE3 as j
join TABLE2 AS k on j."vchagenttaxid" = k."vchAgentTaxID"
where j."bFetch" = false
GROUP BY
j."vchFileName"
,"vchContractEntityRole"
, "vchContractEntityRole"
,j."vchContractNumber"
,"vchContractEntityPersonalIdentifier"
;
i can see a lot of
Re: *Regarding brin_index on required column of the table
On Fri, Sep 21, 2018 at 9:12 PM Andreas Kretschmer wrote: > > > Am 21.09.2018 um 17:13 schrieb Durgamahesh Manne: > > query is below > > query and plan still not readable. Store it into a textfile and attach > it here. > > > Andreas > > -- > 2ndQuadrant - The PostgreSQL Support Company. > www.2ndQuadrant.com > > > Hi Please find below attached query plan file Regards Durgamahesh Manne Query Description: Binary data
Re: *Regarding brin_index on required column of the table
Am 21.09.2018 um 17:13 schrieb Durgamahesh Manne: query is below query and plan still not readable. Store it into a textfile and attach it here. Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: *Regarding brin_index on required column of the table
Your plan is not readable to me (perhaps because of gmail). Does https://explain.depesz.com/ give you any useful insights? On Fri, 21 Sep 2018 at 16:15, Durgamahesh Manne wrote: > > > On Fri, Sep 21, 2018 at 7:38 PM Durgamahesh Manne > wrote: >> >> Hi >> >> Complex query taken around 30 minutes to execute even i have increased >> work_mem value to 4GB temporarily as total ram is 16gb >> >> Explain analyze query taken around 30 minutes to execute even i have created >> partial indexes with where condition on required columns >> >> >> >> Below is the query plan for explain analyze query : >> >> | HashAggregate (cost=16034967.98..16035010.75 rows=4277 width=1710) >> (actual time=1806653.536..1806680.802 rows=26098 loops=1) >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> | >> | Group Key: (max((v."vchSubmittersCode")::text)), >> (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), >> v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", >> (max((v."vchPartyNatural_Non_NaturalEntity")::text)), >> (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), >> (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), >> (max((v."vchPartySuffix")::text)), (NULL::text), v."vchPartyID", >> (max((v."vchPartyIDQualifier")::text)), >> (max((v."vchTrustRevocabilityIndicator")::text)), (NULL::text), >> (NULL::text), (max((v."vchFiller1")::text)), >> (max((v."vchRejectCode")::text)), >> (max((v."vchContractEntityAddressLine1")::text)), >> (max((v."vchContractEntityAddressLine2")::text)), >> (max((v."vchContractEntityCity")::text)), >> (max((v."vchContractEntityState")::text)), >> (max((v."vchContractEntityZip")::text)), >> (max((v."vchContractEntityAddressLine3")::text)), >> (max((v."vchContractEntityAddressLine4")::text)), >> (max((v."vchContractEntityAddressLine5")::text)), >> (max((v."vchPartyDateofBirth")::text)), >> (max((v."vchPartyAddressLine1")::text)), >> (max((v."vchContractStatus")::text)), (string_agg(DISTINCT >> btrim((s."vchAgentTaxID")::text), ','::text)), v."vchPartyRole", >> (max((v."vchAdvisorLabel")::text)), v."vchFileName", >> (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), >> (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT >> btrim((s."vchAgentFirstName")::text) || ' '::text) || >> btrim((s."vchAgentMiddleName")::text)) || ' '::text) || >> btrim((s."vchAgentLastName")::text)), ','::text)) | >> | -> Append (cost=48944.67..16034550.97 rows=4277 width=1710) (actual >> time=3324.233..1806605.691 rows=26098 loops=1) >> >> >> >> >> >> >> >> >> >> >>
Re: *Regarding brin_index on required column of the table
On Fri, Sep 21, 2018 at 7:38 PM Durgamahesh Manne wrote: > Hi > > Complex query taken around 30 minutes to execute even i have > increased work_mem value to 4GB temporarily as total ram is 16gb > > Explain analyze query taken around 30 minutes to execute even i have > created partial indexes with where condition on required columns > > > > Below is the query plan for explain analyze query : > > | HashAggregate (cost=16034967.98..16035010.75 rows=4277 width=1710) > (actual time=1806653.536..1806680.802 rows=26098 loops=1) > > > > > > > > > > > > > > > > > > > | > | Group Key: (max((v."vchSubmittersCode")::text)), > (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), > v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", > (max((v."vchPartyNatural_Non_NaturalEntity")::text)), > (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), > (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), > (max((v."vchPartySuffix")::text)), (NULL::text), v."vchPartyID", > (max((v."vchPartyIDQualifier")::text)), > (max((v."vchTrustRevocabilityIndicator")::text)), (NULL::text), > (NULL::text), (max((v."vchFiller1")::text)), > (max((v."vchRejectCode")::text)), > (max((v."vchContractEntityAddressLine1")::text)), > (max((v."vchContractEntityAddressLine2")::text)), > (max((v."vchContractEntityCity")::text)), > (max((v."vchContractEntityState")::text)), > (max((v."vchContractEntityZip")::text)), > (max((v."vchContractEntityAddressLine3")::text)), > (max((v."vchContractEntityAddressLine4")::text)), > (max((v."vchContractEntityAddressLine5")::text)), > (max((v."vchPartyDateofBirth")::text)), > (max((v."vchPartyAddressLine1")::text)), > (max((v."vchContractStatus")::text)), (string_agg(DISTINCT > btrim((s."vchAgentTaxID")::text), ','::text)), v."vchPartyRole", > (max((v."vchAdvisorLabel")::text)), v."vchFileName", > (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), > (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT > btrim((s."vchAgentFirstName")::text) || ' '::text) || > btrim((s."vchAgentMiddleName")::text)) || ' '::text) || > btrim((s."vchAgentLastName")::text)), ','::text)) | > | -> Append (cost=48944.67..16034550.97 rows=4277 width=1710) (actual > time=3324.233..1806605.691 rows=26098 loops=1) > > > > > > > > > > > > > > > > > > > | > | -> Unique (cost=48944.67..49103.43 rows=1764 width=1145) > (actual time=3324.233..3327.824 rows=3637 loops=1) > > > > > > > > > > > > > > > > > > > | > | -> Sort (cost=48944.67..48949.08 rows=1764 width=1145) > (actual time=3324.232..3324.447 rows=3637 loops=1) > > > > > > > > > > > > > > > > > > > | > | Sort Key: (max((v."vchSubmittersCode")::text)), > (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), > v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", > (max((v."vchPartyNatural_Non_NaturalEntity")::text)), > (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), > (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), > (max((v."vchPartySuffix")::text)), v."vchPartyID", > (max((v."vchPartyIDQualifier")::text)), > (max((v."vchTrustRevocabilityIndicator")::text)), > (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), > (max((v."vchContractEntityAddressLine1")::text)), > (max((v."vchContractEntityAddressLine2")::text)), > (max((v."vchContractEntityCity")::text)), > (max((v."vchContractEntityState")::text)), > (max((v."vchContractEntityZip")::text)), > (max((v."vchContractEntityAddressLine3")::text)), > (max((v."vchContractEntityAddressLine4")::text)), > (max((v."vchContractEntityAddressLine5")::text)), > (max((v."vchPartyDateofBirth")::text)), > (max((v."vchPartyAddressLine1")::text)), > (max((v."vchContractStatus")::text)), (string_agg(DISTINCT > btrim((s."vchAgentTaxID")::text), ','::text)), > (max((v."vchAdvisorLabel")::text)), v."vchFileName", > (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), > (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT > btrim((s."vchAgentFirstName")::text) || ' '::text) || > btrim((s."vchAgentMiddleName")::text)) || ' '::text) || > btrim((s."vchAgentLastName")::text)), ','::text)) > | > | Sort Method: quicksort Memory: 3366kB > > > > > > > > > > > > > > > > > > > > | > | -> GroupAggregate (cost=26241.74..48849.55 > rows=1764 width=1145) (actual time=1451.987..3297.428 rows=3637 loops=1) > > > > > > > > > > > > > > > > > > > | > | Group Key: v."vchPartyRole",
Re: *Regarding brin_index on required column of the table
Hi Complex query taken around 30 minutes to execute even i have increased work_mem value to 4GB temporarily as total ram is 16gb Explain analyze query taken around 30 minutes to execute even i have created partial indexes with where condition on required columns Below is the query plan for explain analyze query : | HashAggregate (cost=16034967.98..16035010.75 rows=4277 width=1710) (actual time=1806653.536..1806680.802 rows=26098 loops=1) | | Group Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", (max((v."vchPartyNatural_Non_NaturalEntity")::text)), (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), (max((v."vchPartySuffix")::text)), (NULL::text), v."vchPartyID", (max((v."vchPartyIDQualifier")::text)), (max((v."vchTrustRevocabilityIndicator")::text)), (NULL::text), (NULL::text), (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), (max((v."vchContractEntityAddressLine1")::text)), (max((v."vchContractEntityAddressLine2")::text)), (max((v."vchContractEntityCity")::text)), (max((v."vchContractEntityState")::text)), (max((v."vchContractEntityZip")::text)), (max((v."vchContractEntityAddressLine3")::text)), (max((v."vchContractEntityAddressLine4")::text)), (max((v."vchContractEntityAddressLine5")::text)), (max((v."vchPartyDateofBirth")::text)), (max((v."vchPartyAddressLine1")::text)), (max((v."vchContractStatus")::text)), (string_agg(DISTINCT btrim((s."vchAgentTaxID")::text), ','::text)), v."vchPartyRole", (max((v."vchAdvisorLabel")::text)), v."vchFileName", (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT btrim((s."vchAgentFirstName")::text) || ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) || btrim((s."vchAgentLastName")::text)), ','::text)) | | -> Append (cost=48944.67..16034550.97 rows=4277 width=1710) (actual time=3324.233..1806605.691 rows=26098 loops=1) | | -> Unique (cost=48944.67..49103.43 rows=1764 width=1145) (actual time=3324.233..3327.824 rows=3637 loops=1) | | -> Sort (cost=48944.67..48949.08 rows=1764 width=1145) (actual time=3324.232..3324.447 rows=3637 loops=1) | | Sort Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", (max((v."vchPartyNatural_Non_NaturalEntity")::text)), (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), (max((v."vchPartySuffix")::text)), v."vchPartyID", (max((v."vchPartyIDQualifier")::text)), (max((v."vchTrustRevocabilityIndicator")::text)), (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), (max((v."vchContractEntityAddressLine1")::text)), (max((v."vchContractEntityAddressLine2")::text)), (max((v."vchContractEntityCity")::text)), (max((v."vchContractEntityState")::text)), (max((v."vchContractEntityZip")::text)), (max((v."vchContractEntityAddressLine3")::text)), (max((v."vchContractEntityAddressLine4")::text)), (max((v."vchContractEntityAddressLine5")::text)), (max((v."vchPartyDateofBirth")::text)), (max((v."vchPartyAddressLine1")::text)), (max((v."vchContractStatus")::text)), (string_agg(DISTINCT btrim((s."vchAgentTaxID")::text), ','::text)), (max((v."vchAdvisorLabel")::text)), v."vchFileName", (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT btrim((s."vchAgentFirstName")::text) || ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) || btrim((s."vchAgentLastName")::text)), ','::text)) | | Sort Method: quicksort Memory: 3366kB | | -> GroupAggregate (cost=26241.74..48849.55 rows=1764 width=1145) (actual time=1451.987..3297.428 rows=3637 loops=1) | | Group Key: v."vchPartyRole", v."vchFileName", v."vchPartyID", v."vchContractPartyRoleQualifier", v."vchContractNumber" | | -> Sort (cost=26241.74..26754.95 rows=205285 width=709) (actual time=1451.641..1474.286 rows=232227 loops=1)
Re: Out of Memory
Well, we are 64-bit I'll see if I can make some kind of self contained test to repeat it. Thanks, Greig -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: array must have even number of elements
čt 20. 9. 2018 v 20:29 odesílatel Susan Hurst <
susan.hu...@brookhurstdata.com> napsal:
> Thanks, everyone!
>
> I get it now. It's not just an array but an hstore array. I changed my
> code to include the original values so now it works:
>
> -- new will be substituted for $1 during execution with using clause
>
> l_query_string := 'select ($1 #= hstore(array[' || l_orig_list || '],'
>
>|| 'array[' || l_clean_list || '])).*';
>
>
>
> Pavel...I am creating a trigger function to look for columns with char,
> varchar or text data types to purge any incoming or updated data of
> extraneous spaces and tabs both within the string and on either end. We
> can use the same function from any table that calls it from a trigger. Now
> that it works, we can refactor it to make it better. I would welcome your
> suggestions for alternatives to hstore.
>
Similar task are not good for plpgsql. You can check PLPerl or PLPythonu,
that is better for these iterations over record.
It can be easy task for C extension.
> Thanks for your help!
>
with pleasure
Pavel
> Sue
>
>
>
> ---
>
> Susan E Hurst
> Principal Consultant
> Brookhurst Data LLC
> Email: susan.hu...@brookhurstdata.com
> Mobile: 314-486-3261
>
> On 2018-09-20 13:04, Pavel Stehule wrote:
>
> Hi
>
> čt 20. 9. 2018 v 19:55 odesílatel Susan Hurst <
> susan.hu...@brookhurstdata.com> napsal:
>
>>
>> Why must an array have an even number of elements? I need to use a
>> trigger function on any table, some of which may have an odd number of
>> columns that I want to cleanse before inserting/updating.
>
>
> The hstore function get parameters as sequence of pairs (key, value) - so
> the number should be even. Odd parameter signalize broken format.
>
> Your example is pretty crazy - I cannot to decode it. Maybe you should to
> use different function, I don't see a sense for using hstore type there.
> But I cannot to decode it.
>
> Regards
>
> Pavel
>
>
>
>
>>
>>
>> Is there a workaround for this?
>>
>>
>> ERROR: array must have even number of elements
>>
>> SQL state: 2202E
>>
>> Context: SQL statement "SELECT ($1 #=
>> hstore(array[trim(replace(regexp_replace($1.c,'( ){2,}',' ','g'),' ','
>> ')),trim(replace(regexp_replace($1.vc,'( ){2,}',' ','g'),' ','
>> ')),trim(replace(regexp_replace($1.t,'( ){2,}',' ','g'),' ',' '))])).*"
>> PL/pgSQL function store.trim_string_before_dml() line 44 at EXECUTE
>>
>>
>>
>> -- my test table
>> create table dm.trg_test (c character(8), vc varchar(16), t text);
>> insert into dm.trg_test (c,vc,t) values ('ctest',' vctest ','
>> ttest ');
>>
>>
>> -- code snippet that produced the error.
>> -- new will be substituted for $1 during execution with using clause
>> l_query_string := 'select ($1 #= hstore(array[' || l_column_list ||
>> '])).*';
>> execute format(l_query_string) using new into new;
>> return new;
>>
>>
>> Thanks for your help!
>>
>> --
>>
>> Susan E Hurst
>> Principal Consultant
>> Brookhurst Data LLC
>> Email: susan.hu...@brookhurstdata.com
>> Mobile: 314-486-3261
>>
>>
