Re: [GENERAL] pgadmin III query

[Dinesh Kumar]

Hi,

On Fri, Dec 6, 2013 at 4:34 PM, Peter Kroon  wrote:

> Thanks, but i need a non command line option.
>
>
We can do this with a function which is having the sql queries of pgAdmin
raised against the database.

=> Log all the queries by enabling "log_minduration_statement=0".
=> Do SELECT pg_reload_conf();
=> Do a refresh on a table of pgAdmin's browser.
=> Get all the queries what it has performed.
=> Create a custom function with those queries.

Regards,
Dinesh


>
> 2013/12/6 Ian Lawrence Barwick 
>
>> 2013/12/6 Peter Kroon :
>> > When you click on a table in the "Object browser" you'll see in the "SQL
>> > pane" the sql that is needed to create that table.
>> >
>> > Which function can I call to get that SQL?
>>
>> You can use the pg_dump command line function for this:
>>
>>   pg_dump -s -t name_of_table name_of_database
>>
>> Regards
>>
>> Ian Barwick
>>
>
>

[GENERAL] network protocol and compatibility question

[Joek Hondius]

Hi All,

We have been running our product on PostgreSQL for quite some time now. 
Started out with 7.0


I have this situation where i want to migrate my (many) PostgreSQL 8.3 
databases to a new server running 9.2

In my case this is not a problem for schema and data.
But i do not want to update the client's drivers if i don't need to 
right now, but prefer to do that in future updates.


Reason is we are moving to new server infrastructure and want to go 9.2,
but the  3rd party making the database driver for our legacy product has 
issues.

This will otherwise keep us from moving to better/supported version.
(insert legacy headache here)

I found that the pgsql network protocol spec has not changed between 8.3 
and 9.2.


Can i use my 8.3 clients on 9.2 servers since the protocol has not 
changed and the database schema remains the same?
I would think yes, i will test but it would be best if backed by some 
kind of theorethical rationale.



Any alternatives?
Can i put pgBouncer 9.2 in front of a 8.3 server for example?

Thanks in advance for looking at this issue.





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

[GENERAL] client that supports editing views

[Willy-Bas Loos]

I have a database with views that have rules on them, so that users can do
insert/update/delete on their part of the data.
The rules ore "do instead" rules that redirect the edits to the table that
actually holds the data, on which the users have no rights.

This works fine in MS Access (at least versions 2000 and 2010).

Now i've added some functionality, i've added a set of views that show
records that a user is entitled to insert, that she hasn't inserted. The
records in the view show the default values, they could be viewed as
"templates". To create the new record, all the user has to do is edit the
template record. It is then inserted into the corresponding table and
consequently disappears from the template view.

But Access 2010 detects the removal from this "template" view as a
conflict: the user did an update, but "another user" deleted the record :( .

Is there a more lightweight windows GUI client that does updates and
deletes on views?

afaik, pgAdmin doesn't allow it.

Cheers,

WBL

-- 
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

Re: [GENERAL] pgadmin III query

[Dinesh Kumar]

Hi Peter,

On Mon, Dec 9, 2013 at 7:03 PM, Peter Kroon  wrote:

> Hi Dinesh,
>
>
> >Get all the queries what it has performed.
>
> How and where?
> When I run "select * from pg_stat_activity" I get the same result with and
> without "log_minduration_statement=0"
>
>
By setting this parameter log_min_duration_statement to 0, postgres will
log all the queries, in the pg_log file.

Hope the following steps helps you on this, and make sure you have enabled
the logging_collector.

1. Modify the above parameter on the required postgres cluster.

2. Do SELECT pg_reload_conf(); on the same machine.

3. And go to pg_log file location, and do tail -f current pg_log file.

4. Go to pgadmin, and refresh on any table.

5. Check the tail -f file output. There you will find all the sql
queries, which have been executed from pgAdmin.

6. Collect those queries, and make your own custom function with pl/pgsql
language.

Regards,
Dinesh


> Could you provide a more detailed step by step guide?
>
> Best,
> Peter
>
>
>
> 2013/12/9 Dinesh Kumar 
>
>> Hi,
>>
>> On Fri, Dec 6, 2013 at 4:34 PM, Peter Kroon  wrote:
>>
>>> Thanks, but i need a non command line option.
>>>
>>>
>> We can do this with a function which is having the sql queries of pgAdmin
>> raised against the database.
>>
>> => Log all the queries by enabling "log_minduration_statement=0".
>> => Do SELECT pg_reload_conf();
>> => Do a refresh on a table of pgAdmin's browser.
>> => Get all the queries what it has performed.
>> => Create a custom function with those queries.
>>
>> Regards,
>> Dinesh
>>
>>
>>>
>>> 2013/12/6 Ian Lawrence Barwick 
>>>
 2013/12/6 Peter Kroon :
 > When you click on a table in the "Object browser" you'll see in the
 "SQL
 > pane" the sql that is needed to create that table.
 >
 > Which function can I call to get that SQL?

 You can use the pg_dump command line function for this:

   pg_dump -s -t name_of_table name_of_database

 Regards

 Ian Barwick

>>>
>>>
>>
>

Re: [GENERAL] client that supports editing views

[Thomas Kellerer]

Willy-Bas Loos, 09.12.2013 14:36:
> I have a database with views that have rules on them, so that users
> can do insert/update/delete on their part of the data. The rules ore
> "do instead" rules that redirect the edits to the table that actually
> holds the data, on which the users have no rights.
> 
> This works fine in MS Access (at least versions 2000 and 2010).
> 
> Now i've added some functionality, i've added a set of views that
> show records that a user is entitled to insert, that she hasn't
> inserted. The records in the view show the default values, they could
> be viewed as "templates". To create the new record, all the user has
> to do is edit the template record. It is then inserted into the
> corresponding table and consequently disappears from the template
> view.
> 
> But Access 2010 detects the removal from this "template" view as a
> conflict: the user did an update, but "another user" deleted the
> record :( .
> 
> Is there a more lightweight windows GUI client that does updates and
> deletes on views?

What kind of "GUI" are you thinking of? 

A GUI focused on running SQL queries or a really something like MS Access where 
you have custom input forms.

Thomas




-- 
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] pgadmin III query

[Peter Kroon]

Hi Dinesh,

SELECT pg_reload_conf();
Did not do the job, I had to restart the server.
I managed to collect the queries and there are a lot of them to show the
SQL that is needed to create the given table.
Does postrgesql have any plan on making their own function for this?

Best,
Peter


2013/12/9 Dinesh Kumar 

> Hi Peter,
>
> On Mon, Dec 9, 2013 at 7:03 PM, Peter Kroon  wrote:
>
>> Hi Dinesh,
>>
>>
>> >Get all the queries what it has performed.
>>
>> How and where?
>> When I run "select * from pg_stat_activity" I get the same result with
>> and without "log_minduration_statement=0"
>>
>>
> By setting this parameter log_min_duration_statement to 0, postgres will
> log all the queries, in the pg_log file.
>
> Hope the following steps helps you on this, and make sure you have enabled
> the logging_collector.
>
> 1. Modify the above parameter on the required postgres cluster.
>
> 2. Do SELECT pg_reload_conf(); on the same machine.
>
> 3. And go to pg_log file location, and do tail -f current pg_log file.
>
> 4. Go to pgadmin, and refresh on any table.
>
> 5. Check the tail -f file output. There you will find all the sql
> queries, which have been executed from pgAdmin.
>
> 6. Collect those queries, and make your own custom function with pl/pgsql
> language.
>
> Regards,
> Dinesh
>
>
>> Could you provide a more detailed step by step guide?
>>
>> Best,
>> Peter
>>
>>
>>
>> 2013/12/9 Dinesh Kumar 
>>
>>> Hi,
>>>
>>> On Fri, Dec 6, 2013 at 4:34 PM, Peter Kroon  wrote:
>>>
 Thanks, but i need a non command line option.


>>> We can do this with a function which is having the sql queries of
>>> pgAdmin raised against the database.
>>>
>>> => Log all the queries by enabling "log_minduration_statement=0".
>>> => Do SELECT pg_reload_conf();
>>> => Do a refresh on a table of pgAdmin's browser.
>>> => Get all the queries what it has performed.
>>> => Create a custom function with those queries.
>>>
>>> Regards,
>>> Dinesh
>>>
>>>

 2013/12/6 Ian Lawrence Barwick 

> 2013/12/6 Peter Kroon :
> > When you click on a table in the "Object browser" you'll see in the
> "SQL
> > pane" the sql that is needed to create that table.
> >
> > Which function can I call to get that SQL?
>
> You can use the pg_dump command line function for this:
>
>   pg_dump -s -t name_of_table name_of_database
>
> Regards
>
> Ian Barwick
>


>>>
>>
>

Re: [GENERAL] Set returning aggregate?

[Rémi Cura]

Hello,
could it be possible then to define a custom CTE as the result of a
function?
somthing like :

with first_cte AS (
select blabla)
, second_cte AS (
a_function_returning_a_set(parameters)
)
SELECT blabla

A CTE is much like what you would like bborie :
you can reference previously defined CTE, and you output a set of row.

Cheers,
Rémi-C


2013/12/8 Pavel Stehule 

> Hello
>
>
> 2013/12/8 Bborie Park 
>
>> I'm wondering if an aggregate function can return a set of records?
>>
>
> No, final function cannot returns set. It is disallowed.
>
> Theoretically, it should be possible - it is explicitly prohibited. But if
> it will be allowed, you can get same problems like using SRF function in
> target list.
>
> postgres=# select generate_series(1,2),generate_series(1,2);
>  generate_series │ generate_series
> ─┼─
>1 │   1
>2 │   2
> (2 rows)
>
> Time: 49.332 ms
> postgres=# select generate_series(1,2),generate_series(1,3);
>  generate_series │ generate_series
> ─┼─
>1 │   1
>2 │   2
>1 │   3
>2 │   1
>1 │   2
>2 │   3
> (6 rows)
>
> Time: 0.445 ms
>
> It will be hard defined a expected behaviour when somebody use more these
> aggregates in same query and returns different number of rows.
>
>
> Regards
>
> Pavel
>
>
>
>>
>> Say I have a table with a column of type raster (PostGIS). I want to get
>> the number of times the pixel values 1, 3 and 4 occur in that raster
>> column. I am hoping to build an aggregrate function that returns the
>> following...
>>
>> value | count
>> +
>> 1   | 12
>> +
>> 2   | 12
>> +
>> 3   | 12
>>
>> Is it possible for an aggregate function to return a set? I've written
>> some test cases and it looks like the answer is No but I'd like
>> confirmation.
>>
>> Thanks,
>> Bborie Park
>>
>> PostGIS Steering Committee
>>
>
>

Re: [GENERAL] client that supports editing views

[Adrian Klaver]

On 12/09/2013 05:36 AM, Willy-Bas Loos wrote:

I have a database with views that have rules on them, so that users can
do insert/update/delete on their part of the data.
The rules ore "do instead" rules that redirect the edits to the table
that actually holds the data, on which the users have no rights.

This works fine in MS Access (at least versions 2000 and 2010).

Now i've added some functionality, i've added a set of views that show
records that a user is entitled to insert, that she hasn't inserted. The
records in the view show the default values, they could be viewed as
"templates". To create the new record, all the user has to do is edit
the template record. It is then inserted into the corresponding table
and consequently disappears from the template view.

But Access 2010 detects the removal from this "template" view as a
conflict: the user did an update, but "another user" deleted the record :( .



Is there a timestamp field in the view? This sounds like an issue Access 
has with timestamp precision, where if you supply a timestamp that is 
too precise it has problems. See here for more detail:


http://www.postgresql.org/message-id/a434c531e37ad442815608a769550d8059425a3...@egexcmb01.oww.root.lcl

Short version, make your timestamp field timestamp(0).



Is there a more lightweight windows GUI client that does updates and
deletes on views?

afaik, pgAdmin doesn't allow it.

Cheers,

WBL

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth



--
Adrian Klaver
adrian.kla...@gmail.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] query creates a huge toast tables

[Steve . Toutant]

Hi Jeff
This query returns 
"tmpcarteprotection4"

tmpcarteprotection4 is old and useless, it could be dropped. Before 
dropping it is there something else I should do?
Thank you very much for your help
Steve





Jeff Janes  
2013-12-02 18:23

A
steve.tout...@inspq.qc.ca
cc
"pgsql-general@postgresql.org" 
Objet
Re: [GENERAL] query creates a huge toast tables








On Thu, Nov 28, 2013 at 8:25 AM,  wrote:

Hi, 
This query (postgis)  didn't endup after 4 hoursand eat 40gig of disk 
space 
SELECT  id, gridcode, ST_Difference(a.geom32198_s,b.geom32198_s) as 
geom32198_s 
into potentialite 
FROM province as a, potentialite_tmp as b; 

I stopped the execution and cancel request sent appears, but after an hour 
the request wasn't stopped yet. 
So I killed the process and restat postgres. 

The database is up and running but I didn't get the 40gig of disk space 
back. And only 5gig remains on the server 

It is this table that is so huge 
pg_toast.pg_toast_11037520 

What table (if any) does it belong to?  "select relname from pg_class 
where oid=11037520"
 
Cheers,

Jeff

Re: [GENERAL] PG replication across DataCenters

[Thomas Harold]

On 11/22/2013 5:57 AM, Albe Laurenz wrote:

Kaushal Shriyan wrote:

I have read on the web that Postgresql DB supports replication
across data centers. Any real life usecase examples if it has been
implemented by anyone.


Well, we replicate a 1 TB database between two locations. It is a
fairly active OLTP application, but certainly not pushing the limits
of what PostgreSQL can do in transactions per second.



Something that section 25 in the pgsql documentation is not clear about 
for hot-standby with WAL log shipping using the built-in streaming:


Can you choose which databases / tables on the master server get 
streamed to the hot-standby read-only server at the remote site?  If 
not, I suspect we'll have to go with either Slony or Bucardo.





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

[GENERAL] Q: regarding backends

[Stephan Fabel]

Hi all,

and sorry if I'm asking a question that has been answered before; has the 
PostgreSQL community ever considered different key/value backends (sort of like 
MySQL with its many different options)?

We'd be very interested in seeing the effects of integrating LMDB [*] in terms 
of performance gains. Has this avenue been explored before?

Thanks,
Stephan

[*] http://symas.com/mdb/



-- 
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] PG replication across DataCenters

[Ben Chobot]

On Dec 9, 2013, at 8:09 AM, Thomas Harold wrote:

> On 11/22/2013 5:57 AM, Albe Laurenz wrote:
>> Kaushal Shriyan wrote:
>>> I have read on the web that Postgresql DB supports replication
>>> across data centers. Any real life usecase examples if it has been
>>> implemented by anyone.
>> 
>> Well, we replicate a 1 TB database between two locations. It is a
>> fairly active OLTP application, but certainly not pushing the limits
>> of what PostgreSQL can do in transactions per second.
>> 
> 
> Something that section 25 in the pgsql documentation is not clear about for 
> hot-standby with WAL log shipping using the built-in streaming:
> 
> Can you choose which databases / tables on the master server get streamed to 
> the hot-standby read-only server at the remote site?  If not, I suspect we'll 
> have to go with either Slony or Bucardo.

No, with the built-in binary replication, it's all or nothing, and the slaves 
have to have the exact same schema as the master (no adding or removing 
indices, for example.)

Out of curiosity what did you find unclear about 
http://www.postgresql.org/docs/9.3/static/different-replication-solutions.html?

Re: [GENERAL] PG replication across DataCenters

[Andreas Kretschmer]

Thomas Harold  wrote:

> On 11/22/2013 5:57 AM, Albe Laurenz wrote:
>> Kaushal Shriyan wrote:
>>> I have read on the web that Postgresql DB supports replication
>>> across data centers. Any real life usecase examples if it has been
>>> implemented by anyone.
>>
>> Well, we replicate a 1 TB database between two locations. It is a
>> fairly active OLTP application, but certainly not pushing the limits
>> of what PostgreSQL can do in transactions per second.
>>
>
> Something that section 25 in the pgsql documentation is not clear about  
> for hot-standby with WAL log shipping using the built-in streaming:
>
> Can you choose which databases / tables on the master server get  
> streamed to the hot-standby read-only server at the remote site?  If  
> not, I suspect we'll have to go with either Slony or Bucardo.

WAL's contains transaction informations for the whole cluster, you can't
choose particular databases or tables.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
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] PG replication across DataCenters (section 25 in the manual)

[Thomas Harold]

On 12/9/2013 11:24 AM, Ben Chobot wrote:


Out of curiosity what did you find unclear about
http://www.postgresql.org/docs/9.3/static/different-replication-solutions.html?


Perhaps the "Per-table granularity" line in the matrix (Table 25-1) 
might be better written as:


"Synchronization Granularity"

Columns 1-3 and 5 could say "Entire Cluster".  Column 4 might say 
"Selected tables (Slony)", and I'm not sure off-hand what granularity #6 
(Bucardo) is capable of.  Column #7 might just say "Varies".


For someone not familiar with what exactly WAL files are, it's not clear 
that solution #3 is an all-or-nothing approaches at the cluster level. 
Now that I've refreshed my memory on how WAL files work (and at what 
level in the pgsql cluster), I understand why #3 works the way it does.



--
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] network protocol and compatibility question

[Steve Atkins]

On Dec 9, 2013, at 2:39 AM, Joek Hondius  wrote:

> Hi All,
> 
> We have been running our product on PostgreSQL for quite some time now. 
> Started out with 7.0
> 
> I have this situation where i want to migrate my (many) PostgreSQL 8.3 
> databases to a new server running 9.2
> In my case this is not a problem for schema and data.
> But i do not want to update the client's drivers if i don't need to right 
> now, but prefer to do that in future updates.
> 
> Reason is we are moving to new server infrastructure and want to go 9.2,
> but the  3rd party making the database driver for our legacy product has 
> issues.
> This will otherwise keep us from moving to better/supported version.
> (insert legacy headache here)
> 
> I found that the pgsql network protocol spec has not changed between 8.3 and 
> 9.2.

It's backwards compatible to at least 8.3.

> 
> Can i use my 8.3 clients on 9.2 servers since the protocol has not changed 
> and the database schema remains the same?
> I would think yes, i will test but it would be best if backed by some kind of 
> theorethical rationale.

The low level network protocol is compatible, but the system tables have 
changed. If your
database driver provides any sort of schema metadata to your app, or uses that 
itself,
then it may fail ungracefully. Without knowing more about your app and the 
database
access library it uses it's hard to say more than that.

> Any alternatives?
> Can i put pgBouncer 9.2 in front of a 8.3 server for example?

That won't have any effect on any issues you might see.

Cheers,
  Steve



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

[GENERAL] "Tuple not found error" during Index creation

[RUSHI KAW]

Hi All,

I have been running Postgresxc 1.1 on a 40 core, 1 TB RAM, Intel(R) Xeon(R) 
machine.
The data tables size with which the database is populated is approx 14GB.
The issue is that during the index creation time on the big table of approx 
13GB size, the following error occurs:

ERROR: could not find tuple for relation 16413.
Statement: Create Index mdx on Network(head)

I read on the forum that this could be because the Autovacuum process may have 
locked the tuple that the indexing process was trying to access. So, I turned 
off the Autovacuum and the error didn't recur after that. 

So, I wanted to ask if this is the only workaround for this error, turning off 
the Autovacuum during index creation and then restarting the process once 
Indexes have been created or is there something else one can do?

Thank you.
Regards,
Rushi Kaw 

Re: [GENERAL] "Tuple not found error" during Index creation

[John R Pierce]

On 12/9/2013 10:07 AM, RUSHI KAW wrote:

I have been running Postgresxc 1.1


you'd probably be best off finding the postgresql-xc list, as that is 
really a rather different system, even if it is forked from community 
postgresql.




--
john r pierce  37N 122W
somewhere on the middle of the left coast

Re: [GENERAL] "Tuple not found error" during Index creation

[Sandeep Gupta]

Agreed. However, the "tuple not found" error problem seems to happen with
postgres as well, if not in the particular case index creation over large
datasets. It would helpful to know in that scenario what are the fixes and
how to avoid it in the first place.

The solution/fixes for postgres will carry over to pgxc because the low
level stuff (blocks, files, indexes) etc. postgres-xc is really the same as
in postgres.

-Sandeep



On Mon, Dec 9, 2013 at 1:23 PM, John R Pierce  wrote:

>  On 12/9/2013 10:07 AM, RUSHI KAW wrote:
>
> I have been running Postgresxc 1.1
>
>
> you'd probably be best off finding the postgresql-xc list, as that is
> really a rather different system, even if it is forked from community
> postgresql.
>
>
>
> --
> john r pierce  37N 122W
> somewhere on the middle of the left coast
>
>

Re: [GENERAL] client that supports editing views

[Willy-Bas Loos]

No, there's no timestamp.
Here's a screenshot of the error in access 2010.
I'll have tot translate it, it's in dutch (and you can't change the
language like you can in ubuntu.. )
It says:
==write conflict==
As you were editing the record, it was changed by another user.
If you save the record, the changes by the other user will be overwritten.

If you want to see the changes made by the other user first, copy the
changes to the Clipboard. After that you can still paste your changes to
the record if you like.

Buttons:
Save record (disabled), Copy to Clipboard, Ignore Changes
--end of error message--

But the option to save the record is disabled, the other 2 buttons do not
enable me to save the record.

Anyway, i don't care much for MS Access, any client will do (that is, if it
costs money, it would be nice if most users own it already or it isn't very
expensive)
I've tried:
* pgAdmin
* MS Access 2010 over ODBC
* LibreOffice.org with the SDBC driver.

Does anyone know of a client that is either smart enough to understand
about rules on views, or transparent enough to let the server handle
everything?

Cheers,

WBL



On Mon, Dec 9, 2013 at 4:16 PM, Adrian Klaver wrote:

> On 12/09/2013 05:36 AM, Willy-Bas Loos wrote:
>
>> I have a database with views that have rules on them, so that users can
>> do insert/update/delete on their part of the data.
>> The rules ore "do instead" rules that redirect the edits to the table
>> that actually holds the data, on which the users have no rights.
>>
>> This works fine in MS Access (at least versions 2000 and 2010).
>>
>> Now i've added some functionality, i've added a set of views that show
>> records that a user is entitled to insert, that she hasn't inserted. The
>> records in the view show the default values, they could be viewed as
>> "templates". To create the new record, all the user has to do is edit
>> the template record. It is then inserted into the corresponding table
>> and consequently disappears from the template view.
>>
>> But Access 2010 detects the removal from this "template" view as a
>> conflict: the user did an update, but "another user" deleted the record
>> :( .
>>
>
>
> Is there a timestamp field in the view? This sounds like an issue Access
> has with timestamp precision, where if you supply a timestamp that is too
> precise it has problems. See here for more detail:
>
> http://www.postgresql.org/message-id/A434C531E37AD442815608A769550D
> 8059425a3...@egexcmb01.oww.root.lcl
>
> Short version, make your timestamp field timestamp(0).
>
>
>
>> Is there a more lightweight windows GUI client that does updates and
>> deletes on views?
>>
>> afaik, pgAdmin doesn't allow it.
>>
>> Cheers,
>>
>> WBL
>>
>> --
>> "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>



-- 
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
<>
-- 
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] "Tuple not found error" during Index creation

[Sandeep Gupta]

Hello All,

 We are trying to trace cause and potential solution of "tuple not found"
error with postgres-xc. The problem happens when indexing a large file. It
seems the autovaccum locks certain cache pages that the indexer tries to
read. The indexing fails with  "tuple not found" error.

I am sure if it qualifies as postgres or postgres-xc error. However, I was
just wondering what is the recommended way to go about fixing this. Turning
off the autovaccumer  is really not the best of solution because then the
system runs into memory usage error.

Would greatly appreciate any pointers on this.

-Sandeep



On Mon, Dec 9, 2013 at 1:43 PM, Sandeep Gupta wrote:

> Agreed. However, the "tuple not found" error problem seems to happen with
> postgres as well, if not in the particular case index creation over large
> datasets. It would helpful to know in that scenario what are the fixes and
> how to avoid it in the first place.
>
> The solution/fixes for postgres will carry over to pgxc because the low
> level stuff (blocks, files, indexes) etc. postgres-xc is really the same as
> in postgres.
>
> -Sandeep
>
>
>
> On Mon, Dec 9, 2013 at 1:23 PM, John R Pierce  wrote:
>
>>  On 12/9/2013 10:07 AM, RUSHI KAW wrote:
>>
>> I have been running Postgresxc 1.1
>>
>>
>> you'd probably be best off finding the postgresql-xc list, as that is
>> really a rather different system, even if it is forked from community
>> postgresql.
>>
>>
>>
>> --
>> john r pierce  37N 122W
>> somewhere on the middle of the left coast
>>
>>
>

Re: [GENERAL] client that supports editing views

[Thomas Kellerer]

Willy-Bas Loos wrote on 09.12.2013 21:44:

But the option to save the record is disabled, the other 2 buttons do not 
enable me to save the record.
Anyway, i don't care much for MS Access, any client will do (that is, if it 
costs money,
it would be nice if most users own it already or it isn't very expensive)

I've tried:
* pgAdmin
* MS Access 2010 over ODBC
* LibreOffice.org with the SDBC driver.

Does anyone know of a client that is either smart enough to understand about 
rules on views,
or transparent enough to let the server handle everything?


If pgAdmin is acceptable, you might want to try SQL Workbench/J: 
http://www.sql-workbench.net

It will allow to run inserts on anything that you can select from and it will 
prompt the user for the PK columns in case none could be identified in the 
database (such as when updating the result of a select based on a view or 
retrieving data from a table without a PK). The selection of the PK columns can 
be saved (locally) to avoid further prompting

Disclosure: I am the author of that tool.

Regards
Thomas






--
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] Q: regarding backends

[Kevin Grittner]

Stephan Fabel  wrote:

> has the PostgreSQL community ever considered different key/value
> backends (sort of like MySQL with its many different options)?
>
> We'd be very interested in seeing the effects of integrating LMDB
> in terms of performance gains.

You might want to consider writing a Foreign Data Wrapper (FDW) to
allow tables defined in LMDB to be accessed from PostgreSQL
queries.

http://wiki.postgresql.org/wiki/Foreign_data_wrappers

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[GENERAL] Replication: GZIP compression in WAL sender/receiver processes communication?

[Dmitry Koterov]

Hello.

Is there a way to compress the traffic between master and slave during the
replication?.. The streaming gzip would be quite efficient for that.

(WAL archiving is not too good for this purpose because of high lag. I just
need to minimize the cross-datacenter traffic keeping the replication lag
low.)

Re: [Postgres-xc-general] [GENERAL] "Tuple not found error" during Index creation

[Michael Paquier]

On Tue, Dec 10, 2013 at 7:17 AM, Sandeep Gupta  wrote:
>  We are trying to trace cause and potential solution of "tuple not found"
> error with postgres-xc. The problem happens when indexing a large file. It
> seems the autovaccum locks certain cache pages that the indexer tries to
> read. The indexing fails with  "tuple not found" error.
>
> I am sure if it qualifies as postgres or postgres-xc error. However, I was
> just wondering what is the recommended way to go about fixing this. Turning
> off the autovaccumer  is really not the best of solution because then the
> system runs into memory usage error.
>
> Would greatly appreciate any pointers on this.
This smells like a concurrency issue with autovacuum on XC side. I
recall fixing in the past issues with autovacuum not taking a correct
snapshot from GTM in certain code paths, putting in danger data
consistency in the cluster as autovacuum might clean more tuples than
it should. Another possibility to explain this bug would be the way
RecentGlobalXmin is computed for autovacuum using the GTM snapshots,
which would explain why autovacuum has cleaned away some tuples it
should not have, making the possibility of a failure higher for
long-running transactions.

Those are assumptions though. It would be great if you could provide a
self-contained test case, with let's imagine a table that has its data
generated with for example generate_series. Just by seeing the spec of
the machine you are using, I am sure that i wouldn't be able to
reproduce that on my laptop though. The core team has access to more
powerful machines.

Also: Postgres-XC 1.1.0 is based on Postgres 9.2.4.
-- 
Michael


-- 
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] Replication: GZIP compression in WAL sender/receiver processes communication?

[Michael Paquier]

On Tue, Dec 10, 2013 at 8:13 AM, Dmitry Koterov  wrote:
> Hello.
>
> Is there a way to compress the traffic between master and slave during the
> replication?.. The streaming gzip would be quite efficient for that.
>
> (WAL archiving is not too good for this purpose because of high lag. I just
> need to minimize the cross-datacenter traffic keeping the replication lag
> low.)
Not directly AFAIK, but this would be a good argument for the
implementation of hooks in walsender and walreceiver that could allow
to use such customization of the stream sent and received.

Note that there is also a patch pending for 9.4 that would make
possible the compression of full page writes, reducing globally the
amount of WAL produced by server in exchange of some CPU work to
compress and decompress the data.
-- 
Michael


-- 
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] Replication: GZIP compression in WAL sender/receiver processes communication?

[John R Pierce]

On 12/9/2013 3:13 PM, Dmitry Koterov wrote:
Is there a way to compress the traffic between master and slave during 
the replication?.. The streaming gzip would be quite efficient for that.


(WAL archiving is not too good for this purpose because of high lag. I 
just need to minimize the cross-datacenter traffic keeping the 
replication lag low.)


run it through a ssh tunnel that has compression turned on ?



--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
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] [pgadmin-support] Lost database

[Dinesh Kumar]

Hi Steve,

On Tue, Dec 10, 2013 at 11:08 AM, Steve Erickson
wrote:

>  While prepping a second server today the /var/lib/postgres/ directory
> got deleted.  Yes, everything.  We’re running on Ubuntu 10.4 and Postgres
> 8.4.  We immediately shutdown everything but now need to recover the
> directory tree.  No, the customer did not do any backups – the database was
> just under 1 TB and they didn’t want to take the time.
>
>
>
> We’re looking into using extundelete or ext4magic to try to recover the
> files and, if successful, are there any further steps we need to take to
> bring up the database again?
>
>
>
> If we’re unsuccessful, we do have a filesystem backup from several months
> ago that we can apply and then go through the tedium of trying to fill in
> the missing blanks for those months.  Since there are so many rows, is
> there a tool or procedure we can use to verify the integrity of the data
> once Postgres is back up and running?
>
>
>
> I don’t expect any miracles, but want to be sure there isn’t less
> intensive alternative out there.
>
>
>
> Thanks,
>
>
>
 Steve Erickson
>

I would like to forward this question to pgsql-general mailing list, since
it's a postgresql related question.

Regards,
Dinesh

Re: [GENERAL] [pgadmin-support] Lost database

[John R Pierce]

On 12/9/2013 10:17 PM, Dinesh Kumar wrote:


If we’re unsuccessful, we do have a filesystem backup from several 
months ago that we can apply and then go through the tedium of trying 
to fill in the missing blanks for those months.  Since there are so 
many rows, is there a tool or procedure we can use to verify the 
integrity of the data once Postgres is back up and running?




if the postgresql server was running when that file backup was made(*), 
its pretty much worthless, it will have data corruption and errors 
throughout.



(*) unless pg_start_backup() was called before the backup, and 
pg_stop_backup() afterwards, which I sort of doubt would have been done 
based on what was written.


--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
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] pgadmin III query

[Dinesh Kumar]

Hi Peter,

On Mon, Dec 9, 2013 at 7:52 PM, Peter Kroon  wrote:

> Hi Dinesh,
>
> SELECT pg_reload_conf();
> Did not do the job, I had to restart the server.
> I managed to collect the queries and there are a lot of them to show the
> SQL that is needed to create the given table.
> Does postrgesql have any plan on making their own function for this?
>

I am not the right person to answer this question. Hope some will give you
answer for this.

I am not sure whether
itworks
for you or not.

Regards,
Dinesh


> Best,
> Peter
>
>

> 2013/12/9 Dinesh Kumar 
>
>> Hi Peter,
>>
>> On Mon, Dec 9, 2013 at 7:03 PM, Peter Kroon  wrote:
>>
>>> Hi Dinesh,
>>>
>>>
>>> >Get all the queries what it has performed.
>>>
>>> How and where?
>>> When I run "select * from pg_stat_activity" I get the same result with
>>> and without "log_minduration_statement=0"
>>>
>>>
>> By setting this parameter log_min_duration_statement to 0, postgres will
>> log all the queries, in the pg_log file.
>>
>> Hope the following steps helps you on this, and make sure you have
>> enabled the logging_collector.
>>
>> 1. Modify the above parameter on the required postgres cluster.
>>
>> 2. Do SELECT pg_reload_conf(); on the same machine.
>>
>> 3. And go to pg_log file location, and do tail -f current pg_log file.
>>
>> 4. Go to pgadmin, and refresh on any table.
>>
>> 5. Check the tail -f file output. There you will find all the sql
>> queries, which have been executed from pgAdmin.
>>
>> 6. Collect those queries, and make your own custom function with pl/pgsql
>> language.
>>
>> Regards,
>> Dinesh
>>
>>
>>> Could you provide a more detailed step by step guide?
>>>
>>> Best,
>>> Peter
>>>
>>>
>>>
>>> 2013/12/9 Dinesh Kumar 
>>>
 Hi,

 On Fri, Dec 6, 2013 at 4:34 PM, Peter Kroon  wrote:

> Thanks, but i need a non command line option.
>
>
 We can do this with a function which is having the sql queries of
 pgAdmin raised against the database.

 => Log all the queries by enabling "log_minduration_statement=0".
 => Do SELECT pg_reload_conf();
 => Do a refresh on a table of pgAdmin's browser.
 => Get all the queries what it has performed.
 => Create a custom function with those queries.

 Regards,
 Dinesh


>
> 2013/12/6 Ian Lawrence Barwick 
>
>> 2013/12/6 Peter Kroon :
>> > When you click on a table in the "Object browser" you'll see in the
>> "SQL
>> > pane" the sql that is needed to create that table.
>> >
>> > Which function can I call to get that SQL?
>>
>> You can use the pg_dump command line function for this:
>>
>>   pg_dump -s -t name_of_table name_of_database
>>
>> Regards
>>
>> Ian Barwick
>>
>
>

>>>
>>
>

[GENERAL] Return setof values from C-function

[Yuriy Rusinov]

Dear Colleagues !

I have to return setof values from C-function

I wrote

FuncCallContext *funcctx;
int call_cntr;
int max_calls;
AttInMetadata *attinmeta;
unsigned long il;
if (SRF_IS_FIRSTCALL())
{
MemoryContext   oldcontext;
funcctx = SRF_FIRSTCALL_INIT();
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
funcctx->max_calls = num;
if (get_call_result_type(fcinfo, NULL, &tupdesc) !=
TYPEFUNC_COMPOSITE)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 errmsg("function returning record called in context "
"that cannot accept type record")));
attinmeta = 0;//TupleDescGetAttInMetadata(tupdesc);
funcctx->attinmeta = 0;//attinmeta;
MemoryContextSwitchTo(oldcontext);
funcctx->tuple_desc = BlessTupleDesc( tupdesc );
elog (INFO, "1st row");
}
funcctx = SRF_PERCALL_SETUP();
call_cntr = funcctx->call_cntr;
max_calls = funcctx->max_calls;
attinmeta = funcctx->attinmeta;
HeapTuple tuple;
if (call_cntr < max_calls)
{
elog (INFO, "Rows");
unsigned long il = call_cntr;
Datum * hvalues = (Datum *)palloc (2*sizeof(Datum));
double val = gsl_histogram_get (gHist, il);
hvalues[0] = UInt32GetDatum (il);
hvalues[1] = Float8GetDatum (val);
bool * nulls = palloc( 2 * sizeof( bool ) );

elog (INFO, "%lu", il);
tuple = heap_form_tuple( tupdesc, hvalues, nulls);
elog (INFO, "%lu", il);
//BuildTupleFromCStrings (attinmeta, hvalues);
pfree (nulls);
pfree (hvalues);
SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum( tuple ) );
}
else
{
SRF_RETURN_DONE(funcctx);
}
as written in documentation, but my function drops on
tuple = heap_form_tuple( tupdesc, hvalues, nulls);
Could you tell me, are there any errors ?

-- 
Best regards,
Sincerely yours,
Yuriy Rusinov.