Re: [HACKERS] [GENERAL] Urgent Help Required

[Chris Travers]

On Wed, Oct 9, 2013 at 7:04 PM, John R Pierce  wrote:

> On 10/8/2013 8:35 AM, Chris Travers wrote:
>
>> First, while vacuum is usually preferred to vacuum full, in this case, I
>> usually find that vacuum full clears up enough cruft to be worth it (not
>> always, but especially if you are also having performance issues).
>>
>
>
> IIRC, vacuum full was pretty broken in 8.1, which the output the original
> postered showed indicated they were running.


I certainly wouldn't recommend it for routine maintenance.  The problem I
have run into is that sometimes folks don't vacuum db's and you find this
out after 7 years of write-heavy workloads.  In this case, there aren't
a lot of great options.  In 8.1 a normal vacuum will usually lead to tons
of bloat in this case because the FSM isn't big enough to accommodate all
the free space which is a problem.  So at that point, vacuum without the
full option is pretty broken in 8.1 :-P  I often find in those cases it is
a choice between vacuum full and dumpall/initdb/reload/analyze.  It is
better now that there is no maximum size for the free space map though.

Best Wishes,
Chris travers

>
>
> --
> 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
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more.shtml

Re: [GENERAL] Incorrect index being used

[Jesse Long]

On 09/10/2013 15:20, Albe Laurenz wrote:

Jesse Long wrote:

There is no problem with row visibility, there is only one connection to
the database - the connection I am using to do these selects.

No idea why the plans cannot be used.

It might be helpful to see the table and index definitions.


Thanks you for the advise regarding ANALYZE. I ran ANALYZE on both
tables concerned, but not much changed:

Did you try increasing default_statistics_target before ANALYZE?

Yours,
Laurenz Albe



Hi Laurenz,

After running VACUUM ANALYZE, the query did run the preferred sub plan, 
at least once. This was before making any changes.


I did ALTER COLUMN SET STATISTICS 1 for each column involved after 
that (is that what you meant?). But it did not make much difference, but 
I have read the manual regarding this setting and think I understand it.


Would it be a bad idea to ALTER COLUMN SET STATISTICS 10? Would that 
have a very negative impact on disk usage or performace?


Thanks,
Jesse






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

[GENERAL] Schema Search Path Problem

[Kalai R]

Hi,

I am developing .Net application using PSQL. I am using npgsql to connect
PSQL database. In single database I have more than one schemas. After I
connect database I set search path using "Set Searchpath command" for the
required schema. It works perfectly.

But after sometime, I need to change schema. so I set again search path
  using "Set Searchpath command" . Now if I execute a single query the
changed search path works correctly. But if I execute a pl/sql function
written in background the changed search path does not work.

Help me to solve this issue.

Thanks.

Re: [GENERAL] Incorrect index being used

[Jesse Long]

On 09/10/2013 18:06, Tom Lane wrote:

Jesse Long  writes:

The query runs for much longer than I expect it to run for, and I think
this is due to it using the incorrect subplan. As you can see, subplans
1 and 3 make use of and index, but these subplans are not used.
Subplans  and 4 are seqscan, and they are used.
How can I get PostgreSQL to use subplan 1 and 3?

You can't, and you would not like the results if you did.

The construct that's being described (perhaps not very intelligibly)
by this EXPLAIN output is an alternative pair of subplans.  Actually
there are two such alternative pairs in this example.  The indexscan
variants are subplans that would be fast if executed only once or
twice.  The seqscan variants, if used, are used to load a hashtable
that is then probed for each row of the outer plan.  If there are a
lot of rows to be considered in the outer plan, then it's better to
pay the price of loading the hashtable, because each hashtable probe
will be a lot cheaper than doing a fresh indexscan with the comparison
value from the current outer row.

In this example, we can see that the outer scan that the subplans
are attached to eliminated 710851 rows by means of the subplan filters,
meaning that the subplans were probed 710851+2 times.  If each of those
probes had been done with a separate indexscan, you'd likely still be
waiting for the result.  Using the seqscan+hashtable was definitely the
right choice here.

BTW, the reason it looks like this rather than just hard-wiring the
seqscan choice is a planner implementation artifact --- at the time
that the subplan plans are created, we don't know how many rows are
expected to pass through the outer plan level.  So we plan it both
ways and leave the choice to be made during executor startup.

What I'd suggest is that you see if you can't get rid of the "EXISTS() OR
EXISTS()" construction in favor of a single EXISTS clause --- I'm too lazy
to work out the details but it looks like you could do the OR in the WHERE
clause of a single EXISTS sub-select.  That would allow the planner to
convert the EXISTS into a semi-join, which might work better than what
you've got.  As is, you're dealing with fairly generic sub-select logic
that isn't going to be terribly well optimized.



Hi Tom,

I am very grateful for your detailed reply. I have not had much time to 
pursue this issue further, but as soon as I have I will investigate and 
study what you have written.


Thanks for taking the time to write your thoughts in detail.

Cheers,
Jesse


--
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] Schema Search Path Problem

[Pavel Stehule]

Hello


2013/10/10 Kalai R 

> Hi,
>
> I am developing .Net application using PSQL. I am using npgsql to connect
> PSQL database. In single database I have more than one schemas. After I
> connect database I set search path using "Set Searchpath command" for the
> required schema. It works perfectly.
>
> But after sometime, I need to change schema. so I set again search path
>   using "Set Searchpath command" . Now if I execute a single query the
> changed search path works correctly. But if I execute a pl/sql function
> written in background the changed search path does not work.
>


It is expected behave for older releases and it is fixed in 9.3. PL/pgSQL
procedures holds a execution plans in session plan cache and changing
search_path didn't invalidate caches. There are two workarounds: a) using
special instances of functions for schema, b) change search_path only once
after connect, and if you need a new search_path, then disconnect, and
create new connect.

Regards

Pavel Stehule


>
> Help me to solve this issue.
>
> Thanks.
>

Re: [GENERAL] streaming replication timeout error

[Stuart Bishop]

On Wed, Oct 9, 2013 at 9:58 AM, 高健  wrote:

> The most important part is:
>
> 2013-09-22 09:52:47 JST[28297][51d1fbcb.6e89-2][0][XX000]FATAL:  Could not
> receive data from WAL stream: could not receive data from server: connection
> timeout
> scp: /opt/PostgresPlus/9.2AS/data/arch/00AC01F1004A: No such
> file or directory
>
> I was asked about:
> In what occasion will the above fatal error occur?

It is a network error. The TCP/IP socket died somehow, and the FATAL
error logged. PostgreSQL then fell back to using the restore_command
defined in your recovery.conf file, but the WAL file had not yet been
shipped and the output logged. I imagine streaming replication happily
reconnected soon after. This is all quite normal.



-- 
Stuart Bishop 
http://www.stuartbishop.net/


-- 
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] Tree structure

[Merlin Moncure]

On Thu, Oct 10, 2013 at 1:00 AM, Kaare Rasmussen  wrote:
> Sorry, got tangled up in this thing called 'real life'.
>
>
>> If I understand you correctly, you want a prefix match, and sure there's
>> a PostgreSQL extension for that:
>
>
> OK, that seems to do the job, thanks a lot. The only small quibble is that
> it's an extension.
>
> I'm quite surprised there seem to be no way in core to treat an array as an
> array. Using @> treats it as a set, AFAICT.

can you elaborate on that?

merlin


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

Re: [HACKERS] [GENERAL] Urgent Help Required

[Kevin Grittner]

Chris Travers  wrote:
> John R Pierce  wrote:

> I often find in those cases it is a choice between vacuum full
> and dumpall/initdb/reload/analyze.

Way back in the 8.1 days I often found CLUSTER to be my best option
-- as long as I had room enough for a second copy (without the
bloat) while it was running.  If I didn't have that much room the
dump/initdb/restore option was almost always faster than VACUUM
FULL.  Personally, I would follow the restore with VACUUM FREEZE
ANALYZE rather than just ANALYZE, so that the entire database
didn't come due for wraparond prevention vacuums at an inopportune
time.

The main thing here seem to be to upgrade to a supported version
(preferably 9.2 or 9.3).

--
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] Feature request: support queries with returning on simple views with automatic update

[Attila Soki]

hi all,

the automatic update for views is really a helpful feature, but i think it 
would be more useful with returning support.

currently we have to construct a rule/trigger to support queries like
'insert into simple_view returning pk'

is it possible (or desired) to implement something like an automatic returning 
support?
it would be great if i could simply write 'insert into simple_view returning 
col1' or 'insert into simple_view returning col2' and postgres would make the 
magic behind.

(a slightly changed text from the pg rules documentation)
The RETURNING logic of the automatic update feature would be used only if the 
automatic update is triggered by an INSERT RETURNING, UPDATE RETURNING, or 
DELETE RETURNING command respectively. When the automatic update is triggered 
by a command without RETURNING, the RETURNING logic will be ignored.

thanks

regards,
Attila




-- 
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] Re: [GENERAL] Forms for entering data into postgresql

[Merlin Moncure]

On Wed, Oct 9, 2013 at 9:24 PM, Adrian Klaver  wrote:
> On 10/09/2013 07:05 PM, Sudhir P.B. wrote:
>>
>> I have developed an application using MS SQL. I have used MS Access for
>> creating forms to enter data into the database. I am thinking of
>> changing over to postgresql and would also like to use any other
>> available open source tool for creating forms. Are there any free
>> applications available for creating forms similar to the ones I have
>> made in MS Access?. Any alternative suggestions will be appreciated.
>
>
> There are tools available, as mentioned in a previous post. There are good
> tools in the list and I have tried quite a few of them. Just know they will
> require a bit more effort then Access to get a finished form. I am not the
> biggest fan of Access, but it does shine in getting fairly involved forms up
> quickly. FYI, you can use it with Postgres, which may be the way to go until
> you have completed the change over to Postgres. Welcome to the Postgres
> community.

Most of the "90's era" application stacks (vb, access, delphi, etc)
will work just fine with postgres over ODBC although an already
written application would need some changes if it's built against the
internall jet database.  If you're writing a new application though,
it's should be clear that investing in a more modern environment is a
good idea.  Forms these days are written in HTML.

merlin


-- 
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] Tree structure

[Kaare Rasmussen]

Hi Merlin


On Thu, Oct 10, 2013 at 1:00 AM, Kaare Rasmussen  wrote:

I'm quite surprised there seem to be no way in core to treat an array as an
array. Using @> treats it as a set, AFAICT.

can you elaborate on that?

merlin


To me, an array is a vector (or a vector of vectors). So I'm looking for 
an operator where


ARRAY[1,4,3] doesn't contain ARRAY[3,1] and
ARRAY[2,7] isn't contained by ARRAY[1,7,4,2,6] (but ARRAY[1,7,4] is)

IOW order matters to me, but not to the array operators mentioned in 
http://www.postgresql.org/docs/9.3/static/functions-array.html. Note 
that index support is important.



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

[GENERAL] Why doesn't update syntax match insert syntax?

[Rob Richardson]

I've been curious about this for a long time.  The syntax for an INSERT query 
is often much easier to use, in my opinion, then the syntax for an UPDATE 
query.  For example, and this is what I am trying to do, assume you have a 
table of inner covers containing a name field and fields named x and y to track 
where each cover is, and you have another table of permissible locations for 
inner covers and other things, with fields containing the name of the stored 
item, its type, and its x and y coordinates.

I am resetting my database to initial conditions, so I am putting the inner 
covers in their storage locations.  I've already updated the storage location 
table, and now I want to update the locations in the inner cover table.  So I 
want to do this:

UPDATE inner_covers (X, Y) 
SELECT sl.X, sl.Y FROM storage_locations sl where sl.name = inner_covers.name

If I were doing an insertion, that syntax would work.  But instead, I'm forced 
to do this:

UPDATE inner_covers
SET X = (SELECT sl.X FROM storage_locations sl where sl.name = 
inner_covers.name),
Y = (SELECT sl.Y FROM storage_locations sl where sl.name = inner_covers.name)

Or is there another, more convenient form of the UPDATE query that I'm not 
familiar with?

Thanks very much!

RobR


-- 
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] Why doesn't update syntax match insert syntax?

[Merlin Moncure]

On Thu, Oct 10, 2013 at 10:03 AM, Rob Richardson
 wrote:
> I've been curious about this for a long time.  The syntax for an INSERT query 
> is often much easier to use, in my opinion, then the syntax for an UPDATE 
> query.  For example, and this is what I am trying to do, assume you have a 
> table of inner covers containing a name field and fields named x and y to 
> track where each cover is, and you have another table of permissible 
> locations for inner covers and other things, with fields containing the name 
> of the stored item, its type, and its x and y coordinates.
>
> I am resetting my database to initial conditions, so I am putting the inner 
> covers in their storage locations.  I've already updated the storage location 
> table, and now I want to update the locations in the inner cover table.  So I 
> want to do this:
>
> UPDATE inner_covers (X, Y)
> SELECT sl.X, sl.Y FROM storage_locations sl where sl.name = inner_covers.name
>
> If I were doing an insertion, that syntax would work.  But instead, I'm 
> forced to do this:
>
> UPDATE inner_covers
> SET X = (SELECT sl.X FROM storage_locations sl where sl.name = 
> inner_covers.name),
> Y = (SELECT sl.Y FROM storage_locations sl where sl.name = inner_covers.name)
>
> Or is there another, more convenient form of the UPDATE query that I'm not 
> familiar with?
>
> Thanks very much!

you have UPDATE FROM:

UPDATE foo SET a=bar.a, b=bar.b
FROM bar WHERE foo.id = bar.id;

merlin


-- 
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] Tree structure

[Rémi Cura]

Hey sorry if my answer is stupid,
but there is an extension for array, even if it is limited to int (but int
could be indexes of row)
It's named http://www.postgresql.org/docs/9.3/static/intarray.html
It provides essential function, although lacking some (I re-implemented
union of array with disjoint result).
I think this extension uses indexes

Cheers,
Rémi-C


2013/10/10 Kaare Rasmussen 

> Hi Merlin
>
>  On Thu, Oct 10, 2013 at 1:00 AM, Kaare Rasmussen 
>> wrote:
>>
>>> I'm quite surprised there seem to be no way in core to treat an array as
>>> an
>>> array. Using @> treats it as a set, AFAICT.
>>>
>> can you elaborate on that?
>>
>> merlin
>>
>
> To me, an array is a vector (or a vector of vectors). So I'm looking for
> an operator where
>
> ARRAY[1,4,3] doesn't contain ARRAY[3,1] and
> ARRAY[2,7] isn't contained by ARRAY[1,7,4,2,6] (but ARRAY[1,7,4] is)
>
> IOW order matters to me, but not to the array operators mentioned in
> http://www.postgresql.org/**docs/9.3/static/functions-**array.html.
> Note that index support is important.
>
>
>
> --
> 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] Tree structure

[Kaare Rasmussen]

Hi Rémi

Hey sorry if my answer is stupid,
but there is an extension for array, even if it is limited to int (but 
int could be indexes of row)

It's named http://www.postgresql.org/docs/9.3/static/intarray.html
It provides essential function, although lacking some (I 
re-implemented union of array with disjoint result).

I think this extension uses indexes



Thanks for your answer. If there were a similar strarray, it would be 
top nice :-)



--
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] Segmentation fault: pg_upgrade 9.1 to 9.3: pg_dump: row number 0 is out of range 0..-1

[Bruce Momjian]

On Sat, Sep 14, 2013 at 09:40:01PM -0400, Robert Nix wrote:
> Running a pg_upgrade task is causing Segmentation fault:
> 
> command: "/usr/lib/postgresql/9.3/bin/pg_dump" --host "/var/lib/postgresql"
> --port 50432 --username "postgres" --schema-only --quote-all-identifiers
> --binary-upgrade --format=custom  --file="pg_upgrade_dump_6064585.custom" "u"
> >> "pg_upgrade_dump_6064585.log" 2>&1
> pg_dump: row number 0 is out of range 0..-1
> Segmentation fault (core dumped)

This error is coming from libpq, specifically
check_tuple_field_number(), which is called by PQgetvalue(),
PQgetlength(), and PQgetisnull().  As I have never seen this error
before, there might be something wrong with the system catalogs on that
cluster.  It would be good to run pg_dump --binary-upgrade on that
database and try to identify the object being dumped that is causing the
crash.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


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

[GENERAL] Can a view represent a schedule for all days into the future?

[Adam Mackler]

Hi:

I recently posted a question on stackoverflow, but I suspect it may
require specific PostgreSQL knowledge, so I'm cross-posting a
reference to it here.

http://stackoverflow.com/questions/19237450/can-sql-view-have-infinite-number-of-rows-repeating-schedule-each-row-a-day

The gist is that I have information representing companies' daily
schedules in some tables, and I want to be able to do a SELECT to get
the business hours for any given day, arbitrarily far into the future
(and past, but I can live with a limit in that direction).  I want to
encapsulate any messy SQL in a single location where it can be hidden
from client usage, presumably in a view.

I currently have a user-defined function that returns the results I
want, but the problem is in the invocation: Some host-language client
libraries aren't so graceful with user-defined functions, especially
when they return multiple rows of multiple columns.  I would like to
be able to do a simple table-style SELECT query, presumably on a view.
But if I can put any date into the WHERE clause, then that means the
view would represent a table with an infinite number of rows, would it
not?

The posting on SO clarifies the specifics of what I'm trying to.  It
seems like there ought to be a way, but I haven't figured it out.

Thanks very much.
-- 
Adam Mackler


-- 
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] Can a view represent a schedule for all days into the future?

[David Johnston]

Adam Mackler-3 wrote
> http://stackoverflow.com/questions/19237450/can-sql-view-have-infinite-number-of-rows-repeating-schedule-each-row-a-day
> 
> I currently have a user-defined function that returns the results I
> want, but the problem is in the invocation: Some host-language client
> libraries aren't so graceful with user-defined functions, especially
> when they return multiple rows of multiple columns.

Not sure how you can state "But I'm willing to agree never to query such a
view without a WHERE clause that restricts the number of rows." when you
cannot even guarantee which host-language client libraries you need to
support.

The use-case you are stating is best solved via the creation of a
user-defined function.  I would implement that and then, in the off chance
there is some kind of client-library interface issue, solve that specific
problem when it arises.  Implementing a less-than-ideal solution today for a
problem that may never even come up is foolish.

More specifically you cannot model infinity in this situation.  The best you
could do is use "generate_series(...)" to construct and appropriately large
domain of values which would then be filtered.

If you want to provide a concrete situation that you must handle and that
the function-invocation form of the API will not work please do so and maybe
some advice can be provided to solve that problem.  Unless and until you can
do that just use the function-invocation form and be content.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Can-a-view-represent-a-schedule-for-all-days-into-the-future-tp5774069p5774076.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] Why doesn't update syntax match insert syntax?

[Marc Mamin]

> On Thu, Oct 10, 2013 at 10:03 AM, Rob Richardson
>  wrote:
> 
> > UPDATE inner_covers
> > SET X = (SELECT sl.X FROM storage_locations sl where sl.name = 
> > inner_covers.name),
> > Y = (SELECT sl.Y FROM storage_locations sl where sl.name = 
> > inner_covers.name)
> >
> > Or is there another, more convenient form of the UPDATE query that I'm not 
> > familiar with?
> >
> > Thanks very much!
> 
> you have UPDATE FROM:
> 
> UPDATE foo SET a=bar.a, b=bar.b
> FROM bar WHERE foo.id = bar.id;
> 
> merlin

Hi,

an alternate syntax which is nearer to the INSERT one:

update foo set (a,b) = (bar.a, bar.b) 
FROM bar 
WHERE foo.id = bar.id;

I guess this is just a question of taste...

regards,

Marc Mamin

-- 
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] Can a view represent a schedule for all days into the future?

[Adam Mackler]

On Thu, Oct 10, 2013 at 10:42:47AM -0700, David Johnston wrote:
> Adam Mackler-3 wrote
> > http://stackoverflow.com/questions/19237450/can-sql-view-have-infinite-number-of-rows-repeating-schedule-each-row-a-day
> > 
> Not sure how you can state "But I'm willing to agree never to query such a
> view without a WHERE clause that restricts the number of rows." when you
> cannot even guarantee which host-language client libraries you need to
> support.

I'm willing to agree that if I ever query such a view without a WHERE
clause that restricts the number of rows, then I won't blame anyone
but myself for the consequences.  If someone can answer this challenge
without imposing that requirement, then I shall be all the more
impressed.

The library I'm using is SLICK:

http://slick.typesafe.com/

As far as I can tell it can't handle user-defined functions that
return multiple rows nor multiple columns, except perhaps through some
low-level SQL interface that would defeat my idea of the purpose of
using a database interface library.

As I mention in the post on SO, my understanding of relational
database theory tells me that tables/relations ARE functions, in the
sense of being a mapping between a primary key and a row.  So there's
nothing ambiguous about issuing a SELECT query for some particular
date, and if I'm only querying for one date, then there's only one day
in the resulting table.

It wouldn't surprise me if there were some way to create a view based
on underlying tables that contain schedules for each day of the week
that I could query in such a fashion for any day arbitrarily far into
the future.  If this is possible, then I would be interested in
knowing what the creation of such a view looks like, independently of
my immediate practical needs.  Call me academic.

--
Adam Mackler


-- 
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] Can a view represent a schedule for all days into the future?

[Merlin Moncure]

On Thu, Oct 10, 2013 at 12:16 PM, Adam Mackler  wrote:
> Hi:
>
> I recently posted a question on stackoverflow, but I suspect it may
> require specific PostgreSQL knowledge, so I'm cross-posting a
> reference to it here.
>
> http://stackoverflow.com/questions/19237450/can-sql-view-have-infinite-number-of-rows-repeating-schedule-each-row-a-day
>
> The gist is that I have information representing companies' daily
> schedules in some tables, and I want to be able to do a SELECT to get
> the business hours for any given day, arbitrarily far into the future
> (and past, but I can live with a limit in that direction).  I want to
> encapsulate any messy SQL in a single location where it can be hidden
> from client usage, presumably in a view.
>
> I currently have a user-defined function that returns the results I
> want, but the problem is in the invocation: Some host-language client
> libraries aren't so graceful with user-defined functions, especially
> when they return multiple rows of multiple columns.  I would like to
> be able to do a simple table-style SELECT query, presumably on a view.
> But if I can put any date into the WHERE clause, then that means the
> view would represent a table with an infinite number of rows, would it
> not?
>
> The posting on SO clarifies the specifics of what I'm trying to.  It
> seems like there ought to be a way, but I haven't figured it out.

The trick for things like this is to cross join generate_series to the
results so that each row of the series is paramaterized through to the
rest of the data.   Postgres is smart enough to optimize that so only
the data reflecting the series element is fetched although you have to
pay for the entire generate_series call (which is normally pretty
cheap).

merlin


-- 
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] Can a view represent a schedule for all days into the future?

[David Johnston]

Adam Mackler-3 wrote
>  If someone can answer this challenge
> without imposing that requirement, then I shall be all the more
> impressed.

 

Fair enough; my goal wasn't to complete a challenge but to actually be
practical.


> The library I'm using is SLICK:
> 
> http://slick.typesafe.com/
> 
> As far as I can tell it can't handle user-defined functions that
> return multiple rows nor multiple columns, except perhaps through some
> low-level SQL interface that would defeat my idea of the purpose of
> using a database interface library.

You seem to be trying to fix the wrong end of the problem then...



> As I mention in the post on SO, my understanding of relational
> database theory tells me that tables/relations ARE functions, in the
> sense of being a mapping between a primary key and a row.  So there's
> nothing ambiguous about issuing a SELECT query for some particular
> date, and if I'm only querying for one date, then there's only one day
> in the resulting table.
> 
> It wouldn't surprise me if there were some way to create a view based
> on underlying tables that contain schedules for each day of the week
> that I could query in such a fashion for any day arbitrarily far into
> the future.  If this is possible, then I would be interested in
> knowing what the creation of such a view looks like, independently of
> my immediate practical needs.  Call me academic.

Yes, there is probably some poorly performing and hard-to-maintain way to
accomplish your goal using triggers, rules and materialized views.

Relational theory only gets you so far.  The actual tools implemented in
PostgreSQL are what we have to work with and given the toolbox in hand, and
my knowledge of it (which is reasonably complete but not perfect) a function
API is, IMO, the most effective solution.  If your method of coding cannot
make use of that API you should decide what you want to do to handle such
since this kind of API is quite common - at least in PostgreSQL which is all
that I am concerned with for purposes of this discussion.  

I'm guessing that your ORM will allow you to handle this use-case in some
manner otherwise it is not a very slick ORM.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Can-a-view-represent-a-schedule-for-all-days-into-the-future-tp5774069p5774115.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] ERROR: invalid value "????" for "YYYY"

[Rowan Collins]

On 10/10/2013 01:57, Brian Wong wrote:
And the extra data that's showing up is being added to the resultset 
cuz without the additional where clause, the result set did not 
contain any of those rows like pg_statistics/etc.


To add to what Brain said on this already, the key thing is that 
Postgres doesn't know (or care) which where clause is "additional". The 
only "original" result set is the one with no where clause at all, which 
contains rows for 'pg_statistics' etc.


--
Rowan Collins
[IMSoP]



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

[GENERAL] A view representing a schedule for all days into the future

[Adam Mackler]

Hi:

I recently posted a question on stackoverflow, but I suspect it may
require specific PostgreSQL knowledge, so I'm cross-posting a
reference to it here.

http://stackoverflow.com/questions/19237450/can-sql-view-have-infinite-number-of-rows-repeating-schedule-each-row-a-day

The gist is that I have information representing companies' daily
schedules in some tables, and I want to be able to do a SELECT to get
the business hours for any given day, arbitrarily far into the future
(and past, but I can live with a limit in that direction).  I want to
encapsulate any messy SQL in a single location where it can be hidden
from client usage, presumably in a view.

I currently have a user-defined function that returns the results I
want, but the problem is in the invocation: Some host-language client
libraries aren't so graceful with user-defined functions, especially
when they return multiple rows of multiple columns.  I would like to
be able to do a simple table-style SELECT query, presumably on a view.
But if I can put any date into the WHERE clause, then that means the
view would represent a table with an infinite number of rows, would it
not?

The posting on SO clarifies the specifics of what I'm trying to.  It
seems like there ought to be a way, but I haven't figured it out.

Thanks very much.
-- 
Adam Mackler


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

[GENERAL] pg_similarity

[janek12]

Now the Steps:

$ USE_PGXS=1 make
$ USE_PGXS=1 make install

are working.

But I still don't have the directory 'SHAREDIR/contrib/pg_similarity.sql'

 

Janek Sendrowski

 

[GENERAL] String reverse funtion?

[ginkgo36]

Hello everyone
 
I have to reverse a string like EA;BX;CA to CA;BX;EA. or EA,BX,CA to
CA,BX,EA
 
Is there any function to do this?

Thanks all!



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/String-reverse-funtion-tp5773871.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] ERROR: invalid value "????" for "YYYY"

[Steve Crawford]

On 10/09/2013 05:57 PM, Brian Wong wrote:
But from a user's perspective, why would it ever make sense that by 
adding an additional where clause, it actually brings in more data 
into the picture?  If I have query returning 100 rows.  Adding an 
additional where clause should only cut down the number of rows, not 
increase it.

...

It may be a bit surprising at first when one does not consider they are 
looking at a view, not at a table, but for the planner to do its job of 
returning the data you want efficiently it must be allowed to optimize 
your request by rewriting it into a logically equivalent form. For 
example, given something like ...where eventtime > 
abstime(12334554321)... the planner will detect that abstime(1234554321) 
will always return the same result and will calculate that value once, 
not redo it once for every row.


The idea that you are just reducing the number of rows doesn't mean that 
doing things in that order is efficient. Imagine that you had a view 
that returned the records of all males from a table of all people in the 
US that includes an indexed birthday field as MMDD. This view would 
return a bit over 155-million rows - roughly 50% of the approximately 
310-million US population. You then query that view to find men who have 
a birthday today. It is obviously suboptimal to first retrieve 
155-million records from disk then evaluate those for the appropriate 
birthday when you can, instead, start by grabbing 1/365th or less than 
1-million rows which can be efficiently returned using the index then 
evaluate those records for male/female.


It's sort of like sending someone to the store with a shopping list - 
you have no expectation that they will fill the cart in the order you 
put things on the list, just that they will come back with the items on 
the list.


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

Re: [GENERAL] postgreSQL query via JDBC in different OS taking different running time?

[Merlin Moncure]

On Mon, Oct 7, 2013 at 10:35 PM, Kevin Grittner  wrote:
> Aftab Ahmed Chandio  wrote:
>
>> My query processes from JDBC (Java Program) to PostgreSQL. I use
>> system time by invoking java function, I collect one time unit
>> before the query statement perform and second after the execution
>> of query statement.
>> I found 85 ms time unit in DOS (win7) (laptop 4cores). both Java
>> and PostgreSQL installed and invoked on the same machine,
>> respectively.
>> On the other hand, I use same process (separate installation) on
>> linux on 8 cores physical machine with 2times greater then
>> laptop.
>> I found 150 ms. (which is a question for me because the time in
>> Linux environment should give me half of the time taking on
>> laptop)
>> I also make same setting  of postgresql.conf in the linux setup,
>> which is available same in the win7  setup, because win7 setup
>> gives better performance of the query.
>> What do u suggest me, where I need to make performance tuning?
>> which configuration setting must need to modify in the linux?
>> * laptop RAM 4 GB and Linux machine 32 GB
>
> Given a little time, I could probably list 100 plausible reasons
> that could be.  For my part, load balancing a production system
> between PostgreSQL on Windows and on Linux hitting identical
> databases on identical hardware, I saw 30% better performance on
> Linux.

One sneaky way that windows tends to beat linux is that windows has a
low precision high performance timer that linux does not have.   This
affects both java and postgres and particularly tends to show up when
benchmarking with times.

merlin


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

[GENERAL] Donation

[Bret Stern]

I'm amazed how complete pgAdmin, postgreSQL, all the interface options,
and this forum works.

It's time to reflect. I feel like donating. 
(Maybe I'll feel the same about our politicians someday) Nnn!

Is https://www.postgresql.us/donate the place to donate?






-- 
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] Donation

[Joshua D. Drake]

On 10/10/2013 03:01 PM, Bret Stern wrote:


I'm amazed how complete pgAdmin, postgreSQL, all the interface options,
and this forum works.

It's time to reflect. I feel like donating.
(Maybe I'll feel the same about our politicians someday) Nnn!

Is https://www.postgresql.us/donate the place to donate?


Hello,

It depends. If you go to:

http://www.postgresql.org/about/donate/

You can select which non-profit is representative of where you want to 
donate.


Sincerely,

Joshua D. Drake

--
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
   a rose in the deeps of my heart. - W.B. Yeats


--
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] Re: pg_upgrade unrecognized configuration parameter “unix_socket_directory”

[Bruce Momjian]

On Sun, Sep 29, 2013 at 07:12:43AM -0300, Clodoaldo Neto wrote:
> Someone claims to have fixed it patching pg_upgrade:
> 
> http://dba.stackexchange.com/a/50714/6978
> 
> Quoting:
> 
> "My solution is to rebuild the pg_upgrade from sources, with update to file
> contrib/pg_upgrade/server.c:199 where pg_upgrade checks for server version:
> 
> 199:   (GET_MAJOR_VERSION(cluster->major_version) < 903) ?
> 
> , in my case i change it to:
> 
> 199:   (GET_MAJOR_VERSION(cluster->major_version) < 900) ?"
> 
> 
> Clodoaldo

Yep, looks like Fedora has to patch pg_upgrade too.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
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] streaming replication timeout error

[高健]

Thank you all a lot!

I have got it.

Best regards


2013/10/10 Stuart Bishop 

> On Wed, Oct 9, 2013 at 9:58 AM, 高健  wrote:
>
> > The most important part is:
> >
> > 2013-09-22 09:52:47 JST[28297][51d1fbcb.6e89-2][0][XX000]FATAL:  Could
> not
> > receive data from WAL stream: could not receive data from server:
> connection
> > timeout
> > scp: /opt/PostgresPlus/9.2AS/data/arch/00AC01F1004A: No such
> > file or directory
> >
> > I was asked about:
> > In what occasion will the above fatal error occur?
>
> It is a network error. The TCP/IP socket died somehow, and the FATAL
> error logged. PostgreSQL then fell back to using the restore_command
> defined in your recovery.conf file, but the WAL file had not yet been
> shipped and the output logged. I imagine streaming replication happily
> reconnected soon after. This is all quite normal.
>
>
>
> --
> Stuart Bishop 
> http://www.stuartbishop.net/
>

Re: [GENERAL] I need more specific instructions for switching to digest mode for this list

[Bob Futrelle]

Me bad. It was right there in front me.

I'm now in Digest Mode.

 - Bob



On Wed, Oct 9, 2013 at 12:00 PM, Igor Neyman  wrote:

> > -Original Message-
> > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> > ow...@postgresql.org] On Behalf Of Raymond O'Donnell
> > Sent: Wednesday, October 09, 2013 11:54 AM
> > To: Bob Futrelle
> > Cc: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] I need more specific instructions for switching to
> > digest mode for this list
> >
> > On 09/10/2013 16:47, Bob Futrelle wrote:
> > > Trying to switch to the digest didn't work.
> > > How do I find more specific details about switching?
> > >
> > >
> > > On Wed, Oct 9, 2013 at 12:27 AM, Bob Futrelle  > > > wrote:
> > >
> > >   set pgsql-general digest
> > >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
> It says it all right at the bottom:
>
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
>
>

[GENERAL] postgresql9.1.6 core dump

[hiroyuki shiga]

Hello.

I'm having problems postgesql coredump.
Do you have any idea?.

OS:Scientific Linux release 6.1
DB:postgresql91-9.1.6
MEM:4G


(gdb) bt full
#0  SearchCatCacheList (cache=0x1d70440, nkeys=1, v1=,
v2=, v3=, v4=)
at catcache.c:1450
hashValue = 1943050110
hashIndex = 1918
relation = 0x7f6983ede610
scandesc = 0x1db11e0
save_exception_stack = 0x7fffc61a1bd0
save_context_stack = 0x0
local_sigjmp_buf = {{__jmpbuf = {140736516985344,
-1013623458724204964,
  31134224, 140736516986976, 0, 1, 1013713306157690460,
  -1013624314715328932}, __mask_was_saved = 0, __saved_mask =
{__val = {
31148304, 0, 6365529, 2262149739, 31148304, 31148304,
140091178270512,
44, 6370319, 140736516985856, 6437365, 0, 4963415, 1,
13593016,
8070450532247928832
cur_skey = {{sk_flags = 0, sk_attno = 1, sk_strategy = 3,
sk_subtype = 0,
sk_collation = 0, sk_func = {fn_addr = 0x67c230 ,
fn_oid = 62,
  fn_nargs = 2, fn_strict = 1 '\001', fn_retset = 0 '\000',
  fn_stats = 2 '\002', fn_extra = 0x0, fn_mcxt = 0x1d2d840,
fn_expr = 0x0},
sk_argument = 30378016}, {sk_flags = 0, sk_attno = 17,
sk_strategy = 3,
sk_subtype = 0, sk_collation = 0, sk_func = {
  fn_addr = 0x684800 , fn_oid = 679, fn_nargs = 2,
  fn_strict = 1 '\001', fn_retset = 0 '\000', fn_stats = 2
'\002',
  fn_extra = 0x0, fn_mcxt = 0x1d2d840, fn_expr = 0x0},
sk_argument = 0}, {
sk_flags = 0, sk_attno = 2, sk_strategy = 3, sk_subtype = 0,
sk_collation = 0, sk_func = {fn_addr = 0x6846e0 , fn_oid
= 184,
  fn_nargs = 2, fn_strict = 1 '\001', fn_retset = 0 '\000',
  fn_stats = 2 '\002', fn_extra = 0x0, fn_mcxt = 0x1d2d840,
fn_expr = 0x0},
sk_argument = 0}, {sk_flags = 0, sk_attno = 0, sk_strategy = 0,
sk_subtype = 0, sk_collation = 0, sk_func = {fn_addr = 0,
fn_oid = 0,
  fn_nargs = 0, fn_strict = 0 '\000', fn_retset = 0 '\000',
  fn_stats = 0 '\000', fn_extra = 0x0, fn_mcxt = 0x0, fn_expr =
0x0},
sk_argument = 0}}
lHashValue = 2904526133
elt = 0xff
cl = 
ct = 
ctlist = 0x0
ctlist_item = 
nmembers = 
ordered = 1 '\001'
ntp = 0x1cf91d0
oldcxt = 
i = 
#1  0x004beb64 in FuncnameGetCandidates (names=, nargs=1,
argnames=0x0, expand_variadic=1 '\001', expand_defaults=1 '\001') at
namespace.c:714
resultList = 0x0
any_special = 0 '\000'
schemaname = 0x0
funcname = 0x1cf8820 "count"
namespaceId = 0
catlist = 
i = 
#2  0x004f9e10 in func_get_detail (funcname=0x1cf87b0,
fargs=0x1db1140,
fargnames=0x0, nargs=1, argtypes=0x7fffc61a16c0, expand_variadic=1
'\001',
expand_defaults=1 '\001', funcid=0x7fffc61a1874, rettype=0x7fffc61a1878,
retset=0x7fffc61a187f "", nvargs=0x7fffc61a1870,
true_typeids=0x7fffc61a1868,
argdefaults=0x7fffc61a1860) at parse_func.c:950
raw_candidates = 
best_candidate = 
__func__ = "func_get_detail"
#3  0x004fa612 in ParseFuncOrColumn (pstate=0x1cf8e10,
funcname=0x1cf87b0,
fargs=0x1db1140, agg_order=0x0, agg_star=0 '\000', agg_distinct=0
'\000',
func_variadic=0 '\000', over=0x0, is_column=0 '\000', location=13)
at parse_func.c:212
rettype = 
funcid = 
l = 
nextl = 
first_arg = 0x1db1210
nargs = 
nargsplusdefs = 
actual_arg_types = {23, 0, 0, 0, 0, 0, 7392693, 0, 0, 0, 1, 0, 2,
0, 5941475, 0,
  0, 0, 19, 0, 2, 0, 30380112, 0, 1, 0, 30379536, 0, 19, 0,
30380112, 0,
  30379536, 0, 5245868, 0, 0, 0, 31131992, 0, 31131992, 0, 2, 0,
30380112, 0,
  5246540, 0, 7, 0, 7393125, 0, 31134224, 0, 31134224, 0, 0, 0,
31133888, 0, 0,
  0, 30379536, 0, 19, 0, 0, 0, 30378280, 0, 5246937, 0, 31132080,
0, 3, 0,
  30379536, 0, 5246540, 0, 0, 0, 30378120, 0, 0, 0, 30379536, 0,
30378088, 0,
  30378280, 0, 30377096, 0, 5206771, 0, 7, 0, 31134304, 0}
declared_arg_types = 
argnames = 
argdefaults = 
retval = 0x1cf91d4
retset = 
nvargs = 
fdresult = 
__func__ = "ParseFuncOrColumn"
#4  0x004f6a3f in transformFuncCall (pstate=0x1cf8e10,
expr=0x1cf8760)
at parse_expr.c:1242
targs = 
args = 
#5  transformExpr (pstate=0x1cf8e10, expr=0x1cf8760) at parse_expr.c:238
result = 0x0
__func__ = "transformExpr"
#6  0x00502806 in transformTargetEntry (pstate=0x1cf8e10,
node=0x1cf8760,
expr=0x0, colname=, resjunk=)
at parse_target.c:91
No locals.
#7  0x005034b5 in transformTargetList (pstate=0x1cf8e10,
targetlist=) at parse_target.c:162
res = 
p_target = 
o_target = 0x1cf86f0
#8  0x0

Re: [GENERAL] Feature request: support queries with returning on simple views with automatic update

[Michael Paquier]

> it would be great if i could simply write 'insert into simple_view returning 
> col1' or 'insert into simple_view returning col2' and postgres would make the 
> magic behind.
You can do it with 9.3~ servers already. Here is an example:
=# create table aa (a int);
CREATE TABLE
=# insert into aa values (1);
INSERT 0 1
=# create view aav as select * from aa;
CREATE VIEW
=# insert into aav values (2) returning a;
 a
---
 2
(1 row)
INSERT 0 1
=# delete from aav where a = 1 returning a;
 a
---
 1
(1 row)
DELETE 1

Regards,
-- 
Michael


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