Re: Startup process on a hot standby crashes with an error "invalid memory alloc request size 1073741824" while replaying "Standby/LOCK" records

[Kyotaro Horiguchi]

> On Wed, 5 Oct 2022 at 16:30, Tom Lane  wrote:
> > One other point to discuss: should we consider back-patching?  I've
> > got mixed feelings about that myself.  I don't think that cases where
> > this helps significantly are at all mainstream, so I'm kind of leaning
> > to "patch HEAD only".

At Mon, 10 Oct 2022 13:24:34 +0100, Simon Riggs  
wrote in 
> It looks fine to eventually backpatch, since StandbyReleaseLockTree()
> was optimized to only be called when the transaction had actually done
> some AccessExclusiveLocks.
> 
> So the performance loss is minor and isolated to the users of such
> locks, so I see no problems with it.

At Wed, 5 Oct 2022 12:00:55 -0700, Nathan Bossart  
wrote in 
> +1.  It can always be back-patched in the future if there are additional
> reports.

The third +1 from me.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Re: Startup process on a hot standby crashes with an error "invalid memory alloc request size 1073741824" while replaying "Standby/LOCK" records

[Simon Riggs]

On Wed, 5 Oct 2022 at 16:30, Tom Lane  wrote:
>
> Kyotaro Horiguchi  writes:
> > At Tue, 4 Oct 2022 17:15:31 -0700, Nathan Bossart 
> >  wrote in
> >> On Tue, Oct 04, 2022 at 07:53:11PM -0400, Tom Lane wrote:
> >>> After further thought, maybe it'd be better to do it as attached,
> >>> with one long-lived hash table for all the locks.
>
> > First one is straight forward outcome from the current implement but I
> > like the new one.  I agree that it is natural and that the expected
> > overhead per (typical) transaction is lower than both the first one
> > and doing the same operation on a list. I don't think that space
> > inefficiency in that extent doesn't matter since it is the startup
> > process.
>
> To get some hard numbers about this, I made a quick hack to collect
> getrusage() numbers for the startup process (patch attached for
> documentation purposes).  I then ran the recovery/t/027_stream_regress.pl
> test a few times and collected the stats (also attached).  This seems
> like a reasonably decent baseline test, since the core regression tests
> certainly take lots of AccessExclusiveLocks what with all the DDL
> involved, though they shouldn't ever take large numbers at once.  Also
> they don't run long enough for any lock list bloat to occur, so these
> numbers don't reflect a case where the patches would provide benefit.
>
> If you look hard, there's maybe about a 1% user-CPU penalty for patch 2,
> although that's well below the run-to-run variation so it's hard to be
> sure that it's real.  The same comments apply to the max resident size
> stats.  So I'm comforted that there's not a significant penalty here.
>
> I'll go ahead with patch 2 if there's not objection.

Happy to see this change.

> One other point to discuss: should we consider back-patching?  I've
> got mixed feelings about that myself.  I don't think that cases where
> this helps significantly are at all mainstream, so I'm kind of leaning
> to "patch HEAD only".

It looks fine to eventually backpatch, since StandbyReleaseLockTree()
was optimized to only be called when the transaction had actually done
some AccessExclusiveLocks.

So the performance loss is minor and isolated to the users of such
locks, so I see no problems with it.

-- 
Simon Riggshttp://www.EnterpriseDB.com/

Re: Startup process on a hot standby crashes with an error "invalid memory alloc request size 1073741824" while replaying "Standby/LOCK" records

[Nathan Bossart]

On Wed, Oct 05, 2022 at 11:30:22AM -0400, Tom Lane wrote:
> One other point to discuss: should we consider back-patching?  I've
> got mixed feelings about that myself.  I don't think that cases where
> this helps significantly are at all mainstream, so I'm kind of leaning
> to "patch HEAD only".

+1.  It can always be back-patched in the future if there are additional
reports.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

Re: Startup process on a hot standby crashes with an error "invalid memory alloc request size 1073741824" while replaying "Standby/LOCK" records

[Tom Lane]

Kyotaro Horiguchi  writes:
> At Tue, 4 Oct 2022 17:15:31 -0700, Nathan Bossart  
> wrote in 
>> On Tue, Oct 04, 2022 at 07:53:11PM -0400, Tom Lane wrote:
>>> After further thought, maybe it'd be better to do it as attached,
>>> with one long-lived hash table for all the locks.

> First one is straight forward outcome from the current implement but I
> like the new one.  I agree that it is natural and that the expected
> overhead per (typical) transaction is lower than both the first one
> and doing the same operation on a list. I don't think that space
> inefficiency in that extent doesn't matter since it is the startup
> process.

To get some hard numbers about this, I made a quick hack to collect
getrusage() numbers for the startup process (patch attached for
documentation purposes).  I then ran the recovery/t/027_stream_regress.pl
test a few times and collected the stats (also attached).  This seems
like a reasonably decent baseline test, since the core regression tests
certainly take lots of AccessExclusiveLocks what with all the DDL
involved, though they shouldn't ever take large numbers at once.  Also
they don't run long enough for any lock list bloat to occur, so these
numbers don't reflect a case where the patches would provide benefit.

If you look hard, there's maybe about a 1% user-CPU penalty for patch 2,
although that's well below the run-to-run variation so it's hard to be
sure that it's real.  The same comments apply to the max resident size
stats.  So I'm comforted that there's not a significant penalty here.

I'll go ahead with patch 2 if there's not objection.

One other point to discuss: should we consider back-patching?  I've
got mixed feelings about that myself.  I don't think that cases where
this helps significantly are at all mainstream, so I'm kind of leaning
to "patch HEAD only".

regards, tom lane

diff --git a/src/backend/postmaster/startup.c b/src/backend/postmaster/startup.c
index f99186eab7..8fcbf898f9 100644
--- a/src/backend/postmaster/startup.c
+++ b/src/backend/postmaster/startup.c
@@ -32,6 +32,7 @@
 #include "storage/pmsignal.h"
 #include "storage/procsignal.h"
 #include "storage/standby.h"
+#include "tcop/tcopprot.h"
 #include "utils/guc.h"
 #include "utils/memutils.h"
 #include "utils/timeout.h"
@@ -219,6 +220,8 @@ StartupProcExit(int code, Datum arg)
 	/* Shutdown the recovery environment */
 	if (standbyState != STANDBY_DISABLED)
 		ShutdownRecoveryTransactionEnvironment();
+
+	ShowUsage("STARTUP PROCESS STATISTICS");
 }
 
 
@@ -229,6 +232,8 @@ StartupProcExit(int code, Datum arg)
 void
 StartupProcessMain(void)
 {
+	ResetUsage();
+
 	/* Arrange to clean up at startup process exit */
 	on_shmem_exit(StartupProcExit, 0);
 
Startup process stats from 027_stream_regress_standby_1.log

HEAD, --disable-cassert:

2022-10-05 10:57:15.561 EDT [2390709] LOG:  STARTUP PROCESS STATISTICS
2022-10-05 10:57:15.561 EDT [2390709] DETAIL:  ! system usage stats:
!   0.935814 s user, 1.019952 s system, 7.226729 s elapsed
!   [0.936104 s user, 1.019952 s system total]
!   9088 kB max resident size
!   0/430608 [0/430608] filesystem blocks in/out
!   0/916 [0/1026] page faults/reclaims, 0 [0] swaps
!   0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!   9925/180 [9925/180] voluntary/involuntary context switches

2022-10-05 11:02:58.542 EDT [2406908] LOG:  STARTUP PROCESS STATISTICS
2022-10-05 11:02:58.542 EDT [2406908] DETAIL:  ! system usage stats:
!   0.957075 s user, 1.029002 s system, 7.206675 s elapsed
!   [0.957075 s user, 1.029260 s system total]
!   9124 kB max resident size
!   0/430512 [0/430512] filesystem blocks in/out
!   0/919 [0/1028] page faults/reclaims, 0 [0] swaps
!   0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!   9575/174 [9575/175] voluntary/involuntary context switches

2022-10-05 11:03:35.436 EDT [2409026] LOG:  STARTUP PROCESS STATISTICS
2022-10-05 11:03:35.436 EDT [2409026] DETAIL:  ! system usage stats:
!   0.936716 s user, 1.117313 s system, 7.208555 s elapsed
!   [0.936716 s user, 1.117606 s system total]
!   9220 kB max resident size
!   0/429712 [0/429712] filesystem blocks in/out
!   0/917 [0/1028] page faults/reclaims, 0 [0] swaps
!   0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!   9509/178 [9509/178] voluntary/involuntary context switches

with Patch 1:

2022-10-05 10:58:50.088 EDT [2393477] LOG:  STARTUP PROCESS STATISTICS
2022-10-05 10:58:50.088 EDT [2393477] DETAIL:  ! system usage stats:
!   0.938248 s user, 1.103261 s system, 7.269850 s elapsed
!   [0.938498 s user, 1.103261 s system total]
!   9288 kB max resident size
!   0/430912 [0/430912] filesystem blocks in/out
!   0/936 [0/1046] page faults/reclaims, 

Re: Startup process on a hot standby crashes with an error "invalid memory alloc request size 1073741824" while replaying "Standby/LOCK" records

[Kyotaro Horiguchi]

At Tue, 4 Oct 2022 17:15:31 -0700, Nathan Bossart  
wrote in 
> On Tue, Oct 04, 2022 at 07:53:11PM -0400, Tom Lane wrote:
> > I wrote:
> >> PFA a quick-hack fix that solves this issue by making per-transaction
> >> subsidiary hash tables.  That's overkill perhaps; I'm a little worried
> >> about whether this slows down normal cases more than it's worth.
> >> But we ought to do something about this, because aside from the
> >> duplication aspect the current storage of these lists seems mighty
> >> space-inefficient.
> > 
> > After further thought, maybe it'd be better to do it as attached,
> > with one long-lived hash table for all the locks.  This is a shade
> > less space-efficient than the current code once you account for
> > dynahash overhead, but the per-transaction overhead should be lower
> > than the previous patch since we only need to create/destroy a hash
> > table entry not a whole hash table.

First one is straight forward outcome from the current implement but I
like the new one.  I agree that it is natural and that the expected
overhead per (typical) transaction is lower than both the first one
and doing the same operation on a list. I don't think that space
inefficiency in that extent doesn't matter since it is the startup
process.

> This feels like a natural way to solve this problem.  I saw several cases
> of the issue that was fixed with 6301c3a, so I'm inclined to believe this
> usage pattern is actually somewhat common.

So releasing locks becomes somewhat slower? But it seems to still be
far faster than massively repetitive head-removal in a list.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Re: Startup process on a hot standby crashes with an error "invalid memory alloc request size 1073741824" while replaying "Standby/LOCK" records

[Nathan Bossart]

On Tue, Oct 04, 2022 at 07:53:11PM -0400, Tom Lane wrote:
> I wrote:
>> PFA a quick-hack fix that solves this issue by making per-transaction
>> subsidiary hash tables.  That's overkill perhaps; I'm a little worried
>> about whether this slows down normal cases more than it's worth.
>> But we ought to do something about this, because aside from the
>> duplication aspect the current storage of these lists seems mighty
>> space-inefficient.
> 
> After further thought, maybe it'd be better to do it as attached,
> with one long-lived hash table for all the locks.  This is a shade
> less space-efficient than the current code once you account for
> dynahash overhead, but the per-transaction overhead should be lower
> than the previous patch since we only need to create/destroy a hash
> table entry not a whole hash table.

This feels like a natural way to solve this problem.  I saw several cases
of the issue that was fixed with 6301c3a, so I'm inclined to believe this
usage pattern is actually somewhat common.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

Re: Startup process on a hot standby crashes with an error "invalid memory alloc request size 1073741824" while replaying "Standby/LOCK" records

[Tom Lane]

I wrote:
> PFA a quick-hack fix that solves this issue by making per-transaction
> subsidiary hash tables.  That's overkill perhaps; I'm a little worried
> about whether this slows down normal cases more than it's worth.
> But we ought to do something about this, because aside from the
> duplication aspect the current storage of these lists seems mighty
> space-inefficient.

After further thought, maybe it'd be better to do it as attached,
with one long-lived hash table for all the locks.  This is a shade
less space-efficient than the current code once you account for
dynahash overhead, but the per-transaction overhead should be lower
than the previous patch since we only need to create/destroy a hash
table entry not a whole hash table.

regards, tom lane

diff --git a/src/backend/storage/ipc/standby.c b/src/backend/storage/ipc/standby.c
index 9dab931990..7db86f7885 100644
--- a/src/backend/storage/ipc/standby.c
+++ b/src/backend/storage/ipc/standby.c
@@ -42,7 +42,29 @@ int			max_standby_archive_delay = 30 * 1000;
 int			max_standby_streaming_delay = 30 * 1000;
 bool		log_recovery_conflict_waits = false;
 
-static HTAB *RecoveryLockLists;
+/*
+ * Keep track of all the exclusive locks owned by original transactions.
+ * For each known exclusive lock, there is a RecoveryLockEntry in the
+ * RecoveryLockHash hash table.  All RecoveryLockEntrys belonging to a
+ * given XID are chained together so that we can find them easily.
+ * For each original transaction that is known to have any such locks,
+ * there is a RecoveryLockXidEntry in the RecoveryLockXidHash hash table,
+ * which stores the head of the chain of its locks.
+ */
+typedef struct RecoveryLockEntry
+{
+	xl_standby_lock key;		/* hash key: xid, dbOid, relOid */
+	struct RecoveryLockEntry *next; /* chain link */
+} RecoveryLockEntry;
+
+typedef struct RecoveryLockXidEntry
+{
+	TransactionId xid;			/* hash key -- must be first */
+	struct RecoveryLockEntry *head; /* chain head */
+} RecoveryLockXidEntry;
+
+static HTAB *RecoveryLockHash = NULL;
+static HTAB *RecoveryLockXidHash = NULL;
 
 /* Flags set by timeout handlers */
 static volatile sig_atomic_t got_standby_deadlock_timeout = false;
@@ -58,15 +80,6 @@ static XLogRecPtr LogCurrentRunningXacts(RunningTransactions CurrRunningXacts);
 static void LogAccessExclusiveLocks(int nlocks, xl_standby_lock *locks);
 static const char *get_recovery_conflict_desc(ProcSignalReason reason);
 
-/*
- * Keep track of all the locks owned by a given transaction.
- */
-typedef struct RecoveryLockListsEntry
-{
-	TransactionId xid;
-	List	   *locks;
-} RecoveryLockListsEntry;
-
 /*
  * InitRecoveryTransactionEnvironment
  *		Initialize tracking of our primary's in-progress transactions.
@@ -85,16 +98,24 @@ InitRecoveryTransactionEnvironment(void)
 	VirtualTransactionId vxid;
 	HASHCTL		hash_ctl;
 
+	Assert(RecoveryLockHash == NULL);	/* don't run this twice */
+
 	/*
-	 * Initialize the hash table for tracking the list of locks held by each
+	 * Initialize the hash tables for tracking the locks held by each
 	 * transaction.
 	 */
+	hash_ctl.keysize = sizeof(xl_standby_lock);
+	hash_ctl.entrysize = sizeof(RecoveryLockEntry);
+	RecoveryLockHash = hash_create("RecoveryLockHash",
+   64,
+   _ctl,
+   HASH_ELEM | HASH_BLOBS);
 	hash_ctl.keysize = sizeof(TransactionId);
-	hash_ctl.entrysize = sizeof(RecoveryLockListsEntry);
-	RecoveryLockLists = hash_create("RecoveryLockLists",
-	64,
-	_ctl,
-	HASH_ELEM | HASH_BLOBS);
+	hash_ctl.entrysize = sizeof(RecoveryLockXidEntry);
+	RecoveryLockXidHash = hash_create("RecoveryLockXidHash",
+	  64,
+	  _ctl,
+	  HASH_ELEM | HASH_BLOBS);
 
 	/*
 	 * Initialize shared invalidation management for Startup process, being
@@ -140,12 +161,12 @@ void
 ShutdownRecoveryTransactionEnvironment(void)
 {
 	/*
-	 * Do nothing if RecoveryLockLists is NULL because which means that
-	 * transaction tracking has not been yet initialized or has been already
-	 * shutdowned. This prevents transaction tracking from being shutdowned
-	 * unexpectedly more than once.
+	 * Do nothing if RecoveryLockHash is NULL because that means that
+	 * transaction tracking has not yet been initialized or has already been
+	 * shut down.  This makes it safe to have possibly-redundant calls of this
+	 * function during process exit.
 	 */
-	if (RecoveryLockLists == NULL)
+	if (RecoveryLockHash == NULL)
 		return;
 
 	/* Mark all tracked in-progress transactions as finished. */
@@ -154,9 +175,11 @@ ShutdownRecoveryTransactionEnvironment(void)
 	/* Release all locks the tracked transactions were holding */
 	StandbyReleaseAllLocks();
 
-	/* Destroy the hash table of locks. */
-	hash_destroy(RecoveryLockLists);
-	RecoveryLockLists = NULL;
+	/* Destroy the lock hash tables. */
+	hash_destroy(RecoveryLockHash);
+	hash_destroy(RecoveryLockXidHash);
+	RecoveryLockHash = NULL;
+	RecoveryLockXidHash = NULL;
 
 	/* Cleanup our 

Re: Startup process on a hot standby crashes with an error "invalid memory alloc request size 1073741824" while replaying "Standby/LOCK" records

[Tom Lane]

[ redirecting to -hackers because patch attached ]

David Rowley  writes:
> So that confirms there were 950k relations in the xl_standby_locks.
> The contents of that message seem to be produced by standby_desc().
> That should be the same WAL record that's processed by standby_redo()
> which adds the 950k locks to the RecoveryLockListsEntry.

> I'm not seeing why 950k becomes 134m.

I figured out what the problem is.  The standby's startup process
retains knowledge of all these locks in standby.c's RecoveryLockLists
data structure, which *has no de-duplication capability*.  It'll add
another entry to the per-XID list any time it's told about a given
exclusive lock.  And checkpoints cause us to regurgitate the entire
set of currently-held exclusive locks into the WAL.  So if you have
a process holding a lot of exclusive locks, and sitting on them
across multiple checkpoints, standby startup processes will bloat.
It's not a true leak, in that we know where the memory is and
we'll release it whenever we see that XID commit/abort.  And I doubt
that this is a common usage pattern, which probably explains the
lack of previous complaints.  Still, bloat bad.

PFA a quick-hack fix that solves this issue by making per-transaction
subsidiary hash tables.  That's overkill perhaps; I'm a little worried
about whether this slows down normal cases more than it's worth.
But we ought to do something about this, because aside from the
duplication aspect the current storage of these lists seems mighty
space-inefficient.

regards, tom lane

diff --git a/src/backend/storage/ipc/standby.c b/src/backend/storage/ipc/standby.c
index 9dab931990..14b86cd76a 100644
--- a/src/backend/storage/ipc/standby.c
+++ b/src/backend/storage/ipc/standby.c
@@ -42,7 +42,26 @@ int			max_standby_archive_delay = 30 * 1000;
 int			max_standby_streaming_delay = 30 * 1000;
 bool		log_recovery_conflict_waits = false;
 
-static HTAB *RecoveryLockLists;
+/*
+ * Keep track of all the exclusive locks owned by original transactions.
+ * For each original transaction that is known to have any such locks,
+ * there is a RecoveryLockHashEntry in the RecoveryLockHash hash table,
+ * pointing to a subsidiary hash table containing RecoveryLockEntry items.
+ */
+typedef struct RecoveryLockHashEntry
+{
+	TransactionId xid;			/* hash key -- must be first */
+	HTAB	   *hashtab;		/* table of locks belonging to xid */
+} RecoveryLockHashEntry;
+
+typedef struct RecoveryLockEntry
+{
+	/* Both Oids are included in the hash key; there is no payload per se */
+	Oid			dbOid;			/* DB containing table */
+	Oid			relOid;			/* OID of table */
+} RecoveryLockEntry;
+
+static HTAB *RecoveryLockHash = NULL;
 
 /* Flags set by timeout handlers */
 static volatile sig_atomic_t got_standby_deadlock_timeout = false;
@@ -58,15 +77,6 @@ static XLogRecPtr LogCurrentRunningXacts(RunningTransactions CurrRunningXacts);
 static void LogAccessExclusiveLocks(int nlocks, xl_standby_lock *locks);
 static const char *get_recovery_conflict_desc(ProcSignalReason reason);
 
-/*
- * Keep track of all the locks owned by a given transaction.
- */
-typedef struct RecoveryLockListsEntry
-{
-	TransactionId xid;
-	List	   *locks;
-} RecoveryLockListsEntry;
-
 /*
  * InitRecoveryTransactionEnvironment
  *		Initialize tracking of our primary's in-progress transactions.
@@ -85,16 +95,18 @@ InitRecoveryTransactionEnvironment(void)
 	VirtualTransactionId vxid;
 	HASHCTL		hash_ctl;
 
+	Assert(RecoveryLockHash == NULL);	/* don't run this twice */
+
 	/*
 	 * Initialize the hash table for tracking the list of locks held by each
 	 * transaction.
 	 */
 	hash_ctl.keysize = sizeof(TransactionId);
-	hash_ctl.entrysize = sizeof(RecoveryLockListsEntry);
-	RecoveryLockLists = hash_create("RecoveryLockLists",
-	64,
-	_ctl,
-	HASH_ELEM | HASH_BLOBS);
+	hash_ctl.entrysize = sizeof(RecoveryLockHashEntry);
+	RecoveryLockHash = hash_create("RecoveryLockHash",
+   64,
+   _ctl,
+   HASH_ELEM | HASH_BLOBS);
 
 	/*
 	 * Initialize shared invalidation management for Startup process, being
@@ -140,12 +152,12 @@ void
 ShutdownRecoveryTransactionEnvironment(void)
 {
 	/*
-	 * Do nothing if RecoveryLockLists is NULL because which means that
-	 * transaction tracking has not been yet initialized or has been already
-	 * shutdowned. This prevents transaction tracking from being shutdowned
-	 * unexpectedly more than once.
+	 * Do nothing if RecoveryLockHash is NULL because that means that
+	 * transaction tracking has not yet been initialized or has already been
+	 * shut down.  This makes it safe to have possibly-redundant calls of this
+	 * function during process exit.
 	 */
-	if (RecoveryLockLists == NULL)
+	if (RecoveryLockHash == NULL)
 		return;
 
 	/* Mark all tracked in-progress transactions as finished. */
@@ -155,8 +167,8 @@ ShutdownRecoveryTransactionEnvironment(void)
 	StandbyReleaseAllLocks();
 
 	/* Destroy the hash table of locks. */
-	

Re: ERROR: invalid memory alloc request size 1073741824

[Suhal Vemu]

Hi all,


This is gisdb and my POSTGIS VERSION

-
*gisdb=# SELECT PostGIS_version();*
*postgis_version*
*---*
* 2.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1*
*(1 row)*
*-*
*It didn't create the table it rolled back .*

*Actually the table creating command is: *

*raster2pgsql -I -C
 
/home/X/Downloads/20161207T055222_20161207T055323_T42QYL/20161207T055222_20161207T055323_T42QYL.ndvi.tif
postgis.gistesttable6 |psql -h localhost -U  -d gisdb -p 5432*


*output :*
*__*
*:~$ raster2pgsql -I -C
 
/home/X/Downloads/20161207T055222_20161207T055323_T42QYL/20161207T055222_20161207T055323_T42QYL.ndvi.tif
postgis.gistesttable6 |psql -h localhost -U XXX -d gisdb -p 5432*
*Processing 1/1:
/home/suhalvemu/Downloads/20161207T055222_20161207T055323_T42QYL/20161207T055222_20161207T055323_T42QYL.ndvi.tif*
*Password for user cropin_suhal: *
*BEGIN*
*CREATE TABLE*
*ERROR:  invalid memory alloc request size 1073741824*
*ERROR:  current transaction is aborted, commands ignored until end of
transaction block*
*ERROR:  current transaction is aborted, commands ignored until end of
transaction block*
*ERROR:  current transaction is aborted, commands ignored until end of
transaction block*
*ROLLBACK*




On Wed, Apr 11, 2018 at 3:16 PM, Suhal Vemu <su...@cropextechnology.com>
wrote:

> Hi David,
>
> This is gisdb and my POSTGIS VERSION
> 
> -
> *gisdb=# SELECT PostGIS_version();*
> *postgis_version*
> *---*
> * 2.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1*
> *(1 row)*
>
> *-*
> *It didn't create the table it rolled back .*
>
> *Actually the table creating command is: *
>
> *raster2pgsql -I -C
>  
> /home/X/Downloads/20161207T055222_20161207T055323_T42QYL/20161207T055222_20161207T055323_T42QYL.ndvi.tif
> postgis.gistesttable6 |psql -h localhost -U  -d gisdb -p 5432*
>
>
> *output :*
>
> *__*
> *:~$ raster2pgsql -I -C
>  
> /home/X/Downloads/20161207T055222_20161207T055323_T42QYL/20161207T055222_20161207T055323_T42QYL.ndvi.tif
> postgis.gistesttable6 |psql -h localhost -U XXX -d gisdb -p 5432*
> *Processing 1/1:
> /home/suhalvemu/Downloads/20161207T055222_20161207T055323_T42QYL/20161207T055222_20161207T055323_T42QYL.ndvi.tif*
> *Password for user cropin_suhal: *
> *BEGIN*
> *CREATE TABLE*
> *ERROR:  invalid memory alloc request size 1073741824*
> *ERROR:  current transaction is aborted, commands ignored until end of
> transaction block*
> *ERROR:  current transaction is aborted, commands ignored until end of
> transaction block*
> *ERROR:  current transaction is aborted, commands ignored until end of
> transaction block*
> *ROLLBACK*
>
>
>
> On Wed, Apr 11, 2018 at 2:34 PM, David Rowley <
> david.row...@2ndquadrant.com> wrote:
>
>> On 7 April 2018 at 02:14, Suhal Vemu <su...@cropextechnology.com> wrote:
>> > Hi David,
>> >
>> > This is gisdb and my POSTGIS VERSION
>> > 
>> -
>> > gisdb=# SELECT PostGIS_version();
>> > postgis_version
>>
>> Hi Suhal,
>>
>> Please reply including the list. You've not really shown the
>> information I asked for. Best to reply to the list with that
>> information.
>>
>> --
>>  David Rowley   http://www.2ndQuadrant.com/
>>  PostgreSQL Development, 24x7 Support, Training & Services
>>
>
>
>
> --
> Thanks,
> Suhal Vemu
>



-- 
Thanks,
Suhal Vemu

-- 
Please note that this email, including any attachments, is intended solely 
for the individual (s) or entity (ies) to whom they are addressed and may 
contain information that is private, confidential and privileged. In case 
you are not the intended recipient, request you to notify the sender by 
reply mail and delete this email, including any copies or attachments from 
your system. Any unauthorized dissemination, disclosure and/or use of the 
contents of this communication to anyone is strictly prohibited and 
punishable by law.

Re: ERROR: invalid memory alloc request size 1073741824

[Tom Lane]

Suhal Vemu  writes:
> so, i need to import the tif file of memory greater than 500mb at least .

If you're trying to cram that into a single bytea field, it's unsurprising
that it fails.  PG is not designed to work with table rows (let alone
individual fields) that exceed some not-very-large fraction of 1GB.
You could stream the value into a "large object", perhaps.

regards, tom lane

Re: ERROR: invalid memory alloc request size 1073741824

[David Rowley]

On 6 April 2018 at 22:06, Suhal Vemu <su...@cropextechnology.com> wrote:
> ERROR:
> BEGIN
> CREATE TABLE
> ERROR:  invalid memory alloc request size 1073741824

Can we see the full CREATE TABLE command?

Are you able to get the CREATE TABLE to succeed if you try removing
some combination of columns? Start with the PostGIS ones. Are you able
to determine if it's a single column or type which is causing the
issue?

Which PostgreSQL version? the output of SELECT version(); would be good.

> Please note that this email, including any attachments, is intended solely
> for the individual (s) or entity (ies) to whom they are addressed and may
> contain information that is private, confidential and privileged. In case
> you are not the intended recipient, request you to notify the sender by
> reply mail and delete this email, including any copies or attachments from
> your system. Any unauthorized dissemination, disclosure and/or use of the
> contents of this communication to anyone is strictly prohibited and
> punishable by law.

This mailing list is public.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services