Re: [HACKERS] [GENERAL] [SQL] pg_multixact issues

[Dev Kumkar]

On Fri, Sep 19, 2014 at 1:23 PM, Dev Kumkar  wrote:

> Apologies for the delay, was working/troubleshooting same issue and was
> away from my emails. :(
> Regards...
>

Received the database with huge pg_multixact directory of size 21G and
there are ~82,000 files in "pg_multixact/members" and 202 files in
"pg_multixact/offsets" directory.

Did run "vacuum full" on this database and it was successful. However now
am not sure about pg_multixact directory. truncating this directory except
 file results into database start up issues, of course this is not
correct way of truncating.
 FATAL:  could not access status of transaction 13224692

Stumped ! Please provide some comments on how to truncate pg_multixact
files and if there is any impact because of these files on database
performance.

Regards...

Re: [HACKERS] [GENERAL] [SQL] pg_multixact issues

[Dev Kumkar]

On Fri, Sep 19, 2014 at 1:03 PM, Andres Freund 
wrote:

> Yes: Learning some patience. You'd given the previous answer two hours
> before this one. Nobody is paid to work on this list...


Apologies for the delay, was working/troubleshooting same issue and was
away from my emails. :(

Regards...

Re: [HACKERS] [GENERAL] [SQL] pg_multixact issues

[Dev Kumkar]

On Thu, Sep 18, 2014 at 6:20 PM, Dev Kumkar  wrote:

> On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund 
> wrote:
>
>> I don't think that's relevant for you.
>>
>> Did you upgrade the database using pg_upgrade?
>>
>
> That's correct! No, there is no upgrade here.
>
>
>> Can you show pg_controldata output and the output of 'SELECT oid,
>> datname, relfrozenxid, age(relfrozenxid), relminmxid FROM pg_database;'?
>>
>
> Here are the details:
>  oid   datname datfrozenxidage(datfrozenxid)datminmxid
> 16384 myDB1673 10872259 1
>
> Additionally wanted to mention couple more points here:
> When I try to run "vacuum full" on this machine then facing following
> issue:
>  INFO:  vacuuming "myDB.mytable"
>  ERROR:  MultiXactId 3622035 has not been created yet -- apparent
> wraparound
>
> No Select statements are working on this table, is the table corrupt?
>

Any inputs/hints/tips here?

Re: [HACKERS] [GENERAL] Case sensitivity

[Dev Kumkar]

On Thu, Dec 12, 2013 at 12:47 PM, Dev Kumkar wrote:

> + hackers
>
>
>
> On Thu, Dec 12, 2013 at 12:34 PM, Dev Kumkar wrote:
>
>> On Wed, Dec 11, 2013 at 9:47 PM, Dev Kumkar wrote:
>>
>>> Actually for searches lower will work.
>>> But the other important aspect is 'inserts' which would result 2 rows if
>>> the values are 'A' and 'a'. Intent here to have it case insensitive.
>>>
>>> If CITEXT it will update the same row and works.
>>> CITEXT is an alternative but was wondering if there is any other
>>> alternate solution/setting while creating database.
>>>
>>> Also does CITEXT fetch via JDBC works the same way as fetch/set string
>>> values? Any quick comments here.
>>>
>>> http://techie-experience.blogspot.in/2013/04/hibernate-supporting-case-insensitive.html
>>>
>>> Regards...
>>>
>>
>> Also if the key columns are CITEXT is there any performance issues on
>> indexes?
>>
> I am ok with CITEXT but for changing the database design for the
primary/foreign key columns to be CITEXT need some suggestions/comments
regarding performance for inserts/reads.

Awaiting...

Re: [HACKERS] [GENERAL] Case sensitivity

[Dev Kumkar]

+ hackers


On Thu, Dec 12, 2013 at 12:34 PM, Dev Kumkar wrote:

> On Wed, Dec 11, 2013 at 9:47 PM, Dev Kumkar wrote:
>
>> Actually for searches lower will work.
>> But the other important aspect is 'inserts' which would result 2 rows if
>> the values are 'A' and 'a'. Intent here to have it case insensitive.
>>
>> If CITEXT it will update the same row and works.
>> CITEXT is an alternative but was wondering if there is any other
>> alternate solution/setting while creating database.
>>
>> Also does CITEXT fetch via JDBC works the same way as fetch/set string
>> values? Any quick comments here.
>>
>> http://techie-experience.blogspot.in/2013/04/hibernate-supporting-case-insensitive.html
>>
>> Regards...
>>
>
> Also if the key columns are CITEXT is there any performance issues on
> indexes?
>

Re: [HACKERS] Grouping Sets

[Dev Kumkar]

On Thu, Jul 4, 2013 at 7:53 PM, Atri Sharma  wrote:

> On Thu, Jul 4, 2013 at 6:56 PM, Dev Kumkar 
> wrote:
> > Ok, no problem. Will await for any other pointers regarding any related
> > patch here.
> >
> > Currently using UNION to archive similar results but looking if anything
> is
> > already done here.
> >
> > Looks like GROUPING SET was in the TODO list long back.
> >
> http://grokbase.com/t/postgresql/pgsql-general/06aaa4g7cq/cube-rollup-grouping-sets
> >
> > Am I missing anything here?
> >
> > Regards...
>
> Me and RhodiumToad discussed the idea recently, after David Fetter
> suggested that we work on it. We may start work on it soon, haven't
> thought in detail yet though.


Ok, 9.3 feature wise looks all done.

So I believe it will be in any 9.3 + release?

Till then will continue UNION approach as looks like it gives the necessary
functionality. Any loopholes here friends?

Regards...

Re: [HACKERS] Grouping Sets

[Dev Kumkar]

On Thu, Jul 4, 2013 at 6:31 PM, Pavel Stehule wrote:

> Hello
>
> I don't work on this topic now, and my code is not usable for production.


Ok, no problem. Will await for any other pointers regarding any related
patch here.

Currently using UNION to archive similar results but looking if anything is
already done here.

Looks like GROUPING SET was in the TODO list long back.
http://grokbase.com/t/postgresql/pgsql-general/06aaa4g7cq/cube-rollup-grouping-sets

Am I missing anything here?

Regards...

[HACKERS] Grouping Sets

[Dev Kumkar]

 Hello,

Am looking for the patch related to 'Implementation of GROUPING SETS'.
Where can get this from?

Related thread:
http://www.postgresql.org/message-id/162867790905121420p7c910054x24d8e327abd58...@mail.gmail.com

Regards...

Re: [HACKERS] "on existing update" construct

[Dev Kumkar]

Basically I was referring to this link http://mbk.projects.postgresql.org

Any suggestions here?

Thanks!

Re: [HACKERS] "on existing update" construct

[Dev Kumkar]

> Please do not top-post on the PostgreSQL lists. See <
http://idallen.com/topposting.html>
> Also, note that pgsql-hackers is entirely the wrong list for this sort of
question. You should have been asking on pgsql-general.
Apologies for top-post!
Well I thought pgsql-hackers will suggest some new solution was just going
by the description of mailing-list. Its worth looking into
http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql

Let me know and I will post this question on pgsql-general.

> It's been on the TODO list forever.  It's harder to implement well than
> you would think.
Yes, I think it be nice to see this in one of the upcoming releases.

Thanks!

Re: [HACKERS] "on existing update" construct

[Dev Kumkar]

Well ya, stored procedure also was thinking about pre-insert trigger.

Am sure folks here must have gone thru this and had alternatives. Also was
reading about merge_by_key.

On Wed, May 15, 2013 at 12:51 PM, Daniel Farina  wrote:

> On Wed, May 15, 2013 at 11:44 AM, Dev Kumkar 
> wrote:
> > Hello,
> >
> > Is there an alternative of Sybase "on existing update" construct in
> pgsql.
> >
> > "ON DUPLICATE KEY UPDATE" doesn't work.
> >
> > Thanks in advance!
>
> No, you'll have to either handle this in the application or use a
> stored procedure at this time.  The omission of such a construct from
> psql's "\h" command and the manual is not in error.
>

[HACKERS] "on existing update" construct

[Dev Kumkar]

Hello,

Is there an alternative of Sybase "on existing update" construct in pgsql.

"ON DUPLICATE KEY UPDATE" doesn't work.

Thanks in advance!

Regards - Dev

Re: [HACKERS] Facing authentication error on postgres 9.2 -> dblink functions

[Dev Kumkar]

Apologies for posting on multiple lists, surely will take care of this next
time.

Yup adding user=username resolved this. Thanks to 'Laurenz Albe' and you
too.

Regards - Dev

On Wed, Feb 6, 2013 at 7:12 PM, Andrew Dunstan  wrote:

>
> On 02/06/2013 08:09 AM, Dev Kumkar wrote:
>
>> Hello Everyone,
>>
>> I am using postgres 9.2 and when executing function dblink facing a fatal
>> error while trying to execute dblink_connect as follows:
>>
>> /SELECT * FROM dblink_connect('host=127.0.0.1 port=5432 dbname=postgres
>> password=test')/
>>
>> *ERROR*: could not establish connection DETAIL: FATAL: password
>> authentication failed for user "NETWORK SERVICE"
>>
>>
>> What this error is related to? Do I need to modify pg_hba.conf file by
>> any chance?
>>
>> Thanks..
>>
>>
>
> Do NOT send questions to multiple lists. That is a waste of everybody's
> time. So do NOT follow up this email. This question belongs on
> pgsql-general. If you have further questions pleease ask there.
>
> The short answer is that you need to provide the user name in your connect
> string.
>
> cheers
>
> andrew
>

[HACKERS] Facing authentication error on postgres 9.2 -> dblink functions

[Dev Kumkar]

Hello Everyone,

I am using postgres 9.2 and when executing function dblink facing a fatal
error while trying to execute dblink_connect as follows:

   * SELECT * FROM dblink_connect('host=127.0.0.1 port=5432 dbname=postgres
password=test')*

*ERROR*: could not establish connection DETAIL: FATAL: password
authentication failed for user "NETWORK SERVICE"

What this error is related to? Do I need to modify pg_hba.conf file by any
chance?

Thanks..