COPY (SELECT * FROM page WHERE "PublishDate" between '2014-03-01' and
'2014-04-01') TO
'/home/ygg/sql/backup/pagedump.2014-03-01.to.2014-04-01.copy';
gives me:
ERROR: could not open file
"/home/ygg/sql/backup/pagedump.2014-03-01.to.2014-04-01.copy" for
writing: No such file or directory
Running
I got it fella's, thanks. It was a really simple oversight on my part.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I'm running into a strange issue whereby my postgres processes are
slowly creeping to 100% CPU utilization. I'm running
postgresql-server-9.2.3, FreeBSD 8.3-RELEASE-p6, and using the
postgresql-9.2-1002.jdbc4 driver.
I'm not sure what information here is relevant, so I'll give
everything I can as
n for up to several hours, then the
utilization issue repeats. So it's a very odd issue I've run into.
On 4/2/13, David Noel wrote:
> I'm running into a strange issue whereby my postgres processes are
> slowly creeping to 100% CPU utilization. I'm running
> postgres
On 4/2/13, Ian Lawrence Barwick wrote:
> 2013/4/3 David Noel :
>> I'm running into a strange issue whereby my postgres processes are
>> slowly creeping to 100% CPU utilization. I'm running
>> postgresql-server-9.2.3, FreeBSD 8.3-RELEASE-p6, and using the
>
On 4/2/13, Kevin Grittner wrote:
> David Noel wrote:
>
>> 'select * from pg_stat_activity' shows that the queries are not
>> waiting, and are in the idle state.
>
> The process is idle or the process is running the query? If the
> latter, what do you mean
On 4/2/13, John R Pierce wrote:
> On 4/2/2013 3:35 PM, David Noel wrote:
>> The hardware is a Dell PowerEdge 1420, dual Xeon Nocona's, 3.2ghz,
>> 16gb ram. The disks are 4 Kingston HyperX SATA3's attached to a
>> HighPoint RocketRAID 2721 controller, ZFS, RAID10.
On 4/3/13, Kevin Grittner wrote:
> David Noel wrote:
>> On 4/2/13, Kevin Grittner wrote:
>>> David Noel wrote:
>>>
>>>> 'select * from pg_stat_activity' shows that the queries are not
>>>> waiting, and are in the idle state.
>>
On 4/2/13, John R Pierce wrote:
> On 4/2/2013 3:35 PM, David Noel wrote:
>> The hardware is a Dell PowerEdge 1420, dual Xeon Nocona's, 3.2ghz,
>> 16gb ram. The disks are 4 Kingston HyperX SATA3's attached to a
>> HighPoint RocketRAID 2721 controller, ZFS, RAID10.
On 4/3/13, Eduardo Morras wrote:
> a) Perhaps process are waiting to I/O, do you take zfs snapshots? How often?
> It can limit your i/o performance. Check the output of #zpool iostat 5
>
> b) Is the zpool ok? If one of the disks lags behind the others (because
> hardware errors) reconstructing the
printStackTrace();
dbq.rollback();
stmt.close();
stmt2.close();
e.printStackTrace();
return null;
} catch (SQLException e2) {
e2.printStackTrace();
return null;
}
}
}
Running on one machine I'm bypassing the transaction concurrency issue
by synchronizing the method. But the Java concurrency constructs I'm
using here don't scale to multiple machines.
At any rate, have I provided enough information to get the solution
I'm looking for? Have I provided enough financial incentive to get
this implemented? If so, please respond with code here to the list so
multiple people don't implement it and expect to be paid. I can only
pay one person, though if another person fixes a bug in a proposed
solution I'm open to splitting the bounty however seems fair.
Thanks for reading, hope to hear back!
-David Noel
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> ...have you used the "for update" clause in your select statements?
Hi Ralf, thanks for the reply. I was unaware of the "for update"
construct. Thank you!
> My understanding is, that "for update" does what you need.
I've read through the documentation, found a few examples using "for
update" s
27;m
unaware of? Is there something wrong with the query?
We're going to try to test it under Linux too, but that system will
have to be set up first so it might be a while before we know those
results.
Any thoughts would be appreciated,
David Noel
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 4/29/14, David Noel wrote:
> The query I'm running is:
>
> "select page.*, coalesce((select COUNT(*) from sentence where
> sentence."PageURL" = page."URL" group by page."URL"), 0) as
> NoOfSentences from page WHERE "Classi
On 4/29/14, Achilleas Mantzios wrote:
> On 29/04/2014 09:59, David Noel wrote:
>> "select page.*, coalesce((select COUNT(*) from sentence where
>> sentence."PageURL" = page."URL" group by page."URL"), 0) as
>> NoOfSentences from page W
'health'<>'' THEN 'health' ELSE '%' END ORDER BY "PublishDate" DESC
Offset 0 LIMIT 100
Does that make any more sense?
On 4/29/14, David Noel wrote:
> On 4/29/14, Achilleas Mantzios wrote:
>> On 29/04/2014 09:59, David Noel wr
> select p.*, s.NoOfSentences
> from page p,
> lateral (select count(*) as NoOfSentences
> from sentence s
> where s."PageURL" = p."URL") s
> where "Classification" like case ... end
> order by "PublishDate" desc
> limit 100;
Great. Thanks so much!
Could I make it
technically this: '
Sorry for the newbie spam -- I can't run
less-than/greater-than/quotation marks through Google for answers.
On 4/29/14, David Noel wrote:
>> select p.*, s.NoOfSentences
>> from page p,
>> lateral (select count(*) as NoOfSentences
>>
> 'health'<>'' (if that is what you have) means a boolean expression that
> compares the
> literal 'health' with the empty literal '' which is of course always false.
Ah. Gotcha. Thanks. I didn't know you could use a single double
quotation mark in a query -- I thought like in most languages that
> But there are two of them : ' and ' makes ''. If you use only one
> psql/parser will complain.
Ha! Wow. That one totally flew by me. It's not a double quotation mark
(one character), it's a double _single_ quotation mark (two
characters). Yeah, that makes complete sense. Wow. Can't believe I
mis
> Is both server/client running on FreeBSD or Windows, or are you switching
> only part of the stack?
When I run it it's all FreeBSD. When the other developer working on it
runs it it's all Windows.
> It shouldn't get stuck. It might be slower on some platforms, but it
> shouldn't really get stuc
. %10, %20, %50, even %100 differences in
performance are huge, but for something to take nearly 100x -- %1
longer to complete? Something just doesn't seem right.
On Tue, Apr 29, 2014 at 3:38 AM, Vik Fearing wrote:
> On 04/29/2014 09:44 AM, David Noel wrote:
>> Ahh, sorry, copied
The FreeBSD system is running 9.3, the Windows systems are running
9.2. I am waiting on the output from the other developer.
On Tue, Apr 29, 2014 at 8:46 AM, Tom Lane wrote:
> David Noel writes:
>> Both queries are run from a Java project using the latest JDBC driver.
>> The Po
> For 9.3, you can write that as:
>
> select p.*, s.NoOfSentences
> from page p,
> lateral (select count(*) as NoOfSentences
> from sentence s
> where s."PageURL" = p."URL") s
> where "Classification" like case ... end
> order by "PublishDate" desc
> limit 100;
>
>
> There is nothing wrong with LATERALs, they just have no business being
> used here. Sorry for the noise.
Ah. No trouble. In fact I'm glad you chimed in -- it motivated me to
learn about laterals so now I know some new SQL syntax!
--
Sent via pgsql-general mailing list (pgsql-general@postgres
I'm running into the following error message when running initdb (FreeBSD host):
ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.
The database cluster will b
On 12/13/12, Adrian Klaver wrote:
> On 12/13/2012 07:38 AM, David Noel wrote:
>> I'm running into the following error message when running initdb (FreeBSD
>> host):
>>
>> ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug
>> The files bel
> You are doing the above as the database user ex:postgres?
confirmed:
ygg:/usr/home/ygg> whoami
pgsql
> The database user has permissions on /zdb/pgsql/data?
confirmed:
ygg:/usr/home/ygg> ll /zdb/
total 3
drwxrwxrwx 3 cvswheel 3 Dec 12 15:33 cvsroot
drwxrwxrwx 2 pgsql wheel 2 Dec 13
On 12/13/12, Tom Lane wrote:
> David Noel writes:
>> I've tried initdb directly:
>> initdb -D /zdb/pgsql/data
>> ...and still seem to wind up with the error:
>> creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
>> could not open file "pg
On 12/13/12, Adrian Klaver wrote:
> On 12/13/2012 10:47 AM, David Noel wrote:
>> On 12/13/12, Tom Lane wrote:
>
>> ygg:/usr/home/ygg> ll /zdb/pgsql/data/pg_xlog/
>> total 2
>> drwx-- 2 pgsql wheel 2 Dec 13 12:42 archive_status
>>
>
> Diff
Unfortunately no luck there. Still stick with the same error.
On 12/13/12, David Noel wrote:
> On 12/13/12, Adrian Klaver wrote:
>> On 12/13/2012 10:47 AM, David Noel wrote:
>>> On 12/13/12, Tom Lane wrote:
>>
>>> ygg:/usr/home/ygg> ll /zdb/pgsql/data/pg
On 12/13/12, Tom Lane wrote:
> David Noel writes:
>> /zdb is a zfs volume I've created for cvs and postgres.
>
> zfs eh? What happens if you point initdb at a non-zfs volume?
>
> (I"m wondering if zfs has issues with the O_DIRECT flag that we'll
> prob
> Well this has definitely moved up a support level and past anything I
> know about.
> For the record what version of FreeBSD are you running in case someone
> is searching the archives?
Any help is always appreciated.
FreeBSD 8.3-RELEASE-p5. (I wonder if upgrading to 9.1 would do anything..)
> Did you use ports to install postgresql?
Yes
> What is the version of postgresql and freebsd you are using?
postgresql client and server v. 9.2.2. If all else fails I could try
downgrading to a previous version of postgres.
FreeBSD 8.3-RELEASE-p5
> I am getting a different output while runni
On 12/13/12, David Noel wrote:
> I'm running into the following error message when running initdb (FreeBSD
> host):
>
> ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug
> The files belonging to this database system will be owned by user "pgsql&quo
>> Interestingly, I have a second--virtually identical--server that I
>> just tried initdb on. FreeBSD 8.3-RELEASE-p5, postgresql-server-9.2.2.
>> Exact same "FATAL: could not open file pg_xlog" error. So it is
>> reproducible.
>
> Does virtually identical extend to architecture, amd64?
Yes... and
On 12/14/12, Tom Lane wrote:
> David Noel writes:
>> I didn't have any luck with the rc script but I was able to use it to
>> get a ktrace dump as root (ktrace as user pgsql doesn't seem to work).
>> So hopefully that will show something(!)
>
> The
> You need to talk to some FreeBSD kernel hackers about why link()
> might be failing here. Since you see it on UFS too, we can probably
> exonerate the ZFS filesystem-specific code.
>
> I did some googling and found that EPERM can be issued if the filesystem
> doesn't support hard links (which sh
stgresql initdb" works perfectly. Just
> needed to set the correct permissions on /example/pgsql directory.
>
> Amitabh
>
>
>
>
> On Sat, Dec 15, 2012 at 10:32 AM, David Noel
> wrote:
>
>> > You need to talk to some FreeBSD kernel hackers about why lin
I'm running into the following error message when running initdb (FreeBSD host):
ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.
The database cluster wi
40 matches
Mail list logo
