PostgreSQL - Binary Large Objects - Versionsgeschichte

Stefan: /* Introduction */

2012-12-02T23:40:39Z

Introduction

← Nächstältere Version		Version vom 2. Dezember 2012, 23:40 Uhr
Zeile 13:		Zeile 13:
	* How is the LO accessed: as encoded string, as file or stream-based?		* How is the LO accessed: as encoded string, as file or stream-based?
	* If not stored within the table, who maintains the integrity of the LO?		* If not stored within the table, who maintains the integrity of the LO?

			== Importing/Exporting External Binary Files ==

			When using BYTEA and TEXT (and XML) the common way to import/export external files is inside client (e.g. Java/Python) or serverside programms (e.g. PL/Python). It's difficult to handle external files with psql.

			See Stackexchange: [http://dba.stackexchange.com/questions/1742/how-to-insert-file-data-into-a-postgresql-bytea-column]
			[http://dba.stackexchange.com/questions/8172/sql-to-read-xml-from-file-into-postgresql-database].

	== BYTEA ==		== BYTEA ==

Stefan: /* Introduction */

2012-01-09T12:29:39Z

Introduction

← Nächstältere Version		Version vom 9. Januar 2012, 12:29 Uhr
Zeile 7:		Zeile 7:
	# Data type DATALINK (that is just spec., no implementation)		# Data type DATALINK (that is just spec., no implementation)

	PostgreSQL also supports a storage system called "TOAST" that automatically stores values larger than a single database page (typically 8 KB) into a secondary storage area per table. This defines the size limit of of any column/field to 1 GB.		PostgreSQL also supports a storage system called "TOAST" (The Oversized-Attribute Storage Technique) that automatically stores values larger than a single database page (typically 8 KB) into a secondary storage area per table. This defines the size limit of of any column/field to 1 GB.

	Here are some design and implementation questions regarding large objects:		Here are some design and implementation questions regarding large objects:

Stefan am 8. Januar 2012 um 20:05 Uhr

2012-01-08T20:05:10Z

← Nächstältere Version		Version vom 8. Januar 2012, 20:05 Uhr
Zeile 19:		Zeile 19:
	* It stores a LOB within the table, respectively using TOAST.		* It stores a LOB within the table, respectively using TOAST.
	* It is thus limited to 1 GB		* It is thus limited to 1 GB
	* The storage is octal and allows non printable characters (in contrast to character strings which don't~~, but allow locale settings~~).		* The storage is octal and allows non printable characters (in contrast to character strings which don't).
	* The input/output format is HEX (as of PostgreSQL 9.0).		* The input/output format is HEX (as of PostgreSQL 9.0).

Zeile 30:		Zeile 30:
	== TEXT ==		== TEXT ==

	Basic data type ''text'' it's just here for completeness. This is a variable character type with unlimited length (up to 1 GB). It's not a byte string but one could still use it when the binary string is preprocessed and encoded into printable form (e.g. base64 or hex).		Basic data type ''text'' it's just here for completeness. This is a variable character type with unlimited length (up to 1 GB). character types allow locale settings.

			It's not a byte string but one could still use it when the binary string is preprocessed and encoded into printable form (e.g. base64 or hex).

	== Large object (LO) facility ==		== Large object (LO) facility ==

Stefan: /* Large object (LO) facility */

2012-01-08T19:55:01Z

Large object (LO) facility

← Nächstältere Version		Version vom 8. Januar 2012, 19:55 Uhr
Zeile 45:		Zeile 45:
	* In contrast to e.g. BYTEA - LO is not a data type on its own but a table, a 'facility'.		* In contrast to e.g. BYTEA - LO is not a data type on its own but a table, a 'facility'.

	IMPORTANT NOTE when using JDBC BLOB (or @Lob annotation in Hibernate):		'''IMPORTANT NOTE''' when using JDBC BLOB (or @Lob annotation in Hibernate):
	* Since PostgreSQL considers a LO entry as an object on it's own, deleting or upating rows in the user table does'nt delete or delete entries in pg_largeobjects. pg_largeobjects therefore grows infinitely unless a separate cleaning is made.		* Since PostgreSQL considers a LO entry as an object on it's own, deleting or upating rows in the user table does'nt delete or delete entries in pg_largeobjects. pg_largeobjects therefore grows infinitely unless a separate cleaning is made (see this [https://forum.hibernate.org/viewtopic.php?p=2382442 error report in Hibernate forum]).
	* To prevent this, typically a trigger needs to be added which deletes entries in pg_largeobject as descriebd in module 'lo'.		* To prevent this, typically a trigger needs to be added which deletes entries in pg_largeobject as descriebd in module 'lo'.
	* See additional PostgreSQL modules [http://www.postgresql.org/docs/9.1/static/lo.html 'lo'] and [http://www.postgresql.org/docs/9.1/static/vacuumlo.html 'vaccumlo'] in the PostgreSQL docs.		* See additional PostgreSQL modules [http://www.postgresql.org/docs/9.1/static/lo.html 'lo'] and [http://www.postgresql.org/docs/9.1/static/vacuumlo.html 'vaccumlo'] in the PostgreSQL docs.

Stefan: /* TEXT */

2012-01-08T18:30:05Z

TEXT

← Nächstältere Version		Version vom 8. Januar 2012, 18:30 Uhr
Zeile 30:		Zeile 30:
	== TEXT ==		== TEXT ==

	Basic data type ''text'' it's just here for completeness. This is a variable character type with unlimited length (up to 1 GB). It's not a byte string but one could still use it when the binary string is preprocessed and encoded into printable form (e.g. hex).		Basic data type ''text'' it's just here for completeness. This is a variable character type with unlimited length (up to 1 GB). It's not a byte string but one could still use it when the binary string is preprocessed and encoded into printable form (e.g. base64 or hex).

	== Large object (LO) facility ==		== Large object (LO) facility ==

Stefan am 8. Januar 2012 um 18:21 Uhr

2012-01-08T18:21:25Z

← Nächstältere Version		Version vom 8. Januar 2012, 18:21 Uhr
Zeile 8:		Zeile 8:

	PostgreSQL also supports a storage system called "TOAST" that automatically stores values larger than a single database page (typically 8 KB) into a secondary storage area per table. This defines the size limit of of any column/field to 1 GB.		PostgreSQL also supports a storage system called "TOAST" that automatically stores values larger than a single database page (typically 8 KB) into a secondary storage area per table. This defines the size limit of of any column/field to 1 GB.

	Regarding data type ''text'' it's just here for completeness. This is a variable character type with unlimited length (up to 1 GB). It's not a byte string but one could still use it when the binary string is preprocessed and encoded into printable form.

	Here are some design and implementation questions regarding large objects:		Here are some design and implementation questions regarding large objects:
Zeile 29:		Zeile 27:

	See PostgreSQL doc [http://www.postgresql.org/docs/9.1/static/datatype-binary.html Binary Data Types].		See PostgreSQL doc [http://www.postgresql.org/docs/9.1/static/datatype-binary.html Binary Data Types].

			== TEXT ==

			Basic data type ''text'' it's just here for completeness. This is a variable character type with unlimited length (up to 1 GB). It's not a byte string but one could still use it when the binary string is preprocessed and encoded into printable form (e.g. hex).

	== Large object (LO) facility ==		== Large object (LO) facility ==

Stefan am 8. Januar 2012 um 18:19 Uhr

2012-01-08T18:19:59Z

← Nächstältere Version		Version vom 8. Januar 2012, 18:19 Uhr
Zeile 5:		Zeile 5:
	# Basic character data type TEXT		# Basic character data type TEXT
	# Large object (LO) facility 'pg_largeobject'		# Large object (LO) facility 'pg_largeobject'
	# Data type DATALINK (~~experimental~~)		# Data type DATALINK (that is just spec., no implementation)

	PostgreSQL also supports a storage system called "TOAST" that automatically stores values larger than a single database page (typically 8 KB) into a secondary storage area per table. This defines the size limit of of any column/field to 1 GB.		PostgreSQL also supports a storage system called "TOAST" that automatically stores values larger than a single database page (typically 8 KB) into a secondary storage area per table. This defines the size limit of of any column/field to 1 GB.

	Regarding data type ''text'' it's just here for completeness. This is a variable character type with unlimited length (up to 1 GB). ~~One can also store~~ encoded ~~binary codes in it~~.		Regarding data type ''text'' it's just here for completeness. This is a variable character type with unlimited length (up to 1 GB). It's not a byte string but one could still use it when the binary string is preprocessed and encoded into printable form.

	Here are some design and implementation questions regarding large objects:		Here are some design and implementation questions regarding large objects:
Zeile 75:		Zeile 75:
	== DATALINK ==		== DATALINK ==

	The DATALINK type stores file URLs in database columns and applies constraints on it. ~~It's defined in Standard SQL 'SQL/MED' but not yet used widely.~~		NOTE: There is currently no implementation in PostgreSQL for that. It's just a specification defined in Standard SQL 'SQL/MED'.

			The DATALINK type stores file URLs in database columns and applies constraints on it.
	* Maintains a link to a specific file in external storage.		* Maintains a link to a specific file in external storage.
	* Database system takes over control over external files (rename, delete, permissions are done with SQL) if defined so.		* Database system takes over control over external files (rename, delete, permissions are done with SQL) if defined so.

Stefan: /* Large object (LO) facility */

2012-01-08T16:41:47Z

Large object (LO) facility

← Nächstältere Version		Version vom 8. Januar 2012, 16:41 Uhr
Zeile 44:		Zeile 44:

	IMPORTANT NOTE when using JDBC BLOB (or @Lob annotation in Hibernate):		IMPORTANT NOTE when using JDBC BLOB (or @Lob annotation in Hibernate):
	* Since PostgreSQL considers a LO entry as an object on it's own, pg_largeobjects grows infinitely unless a separate cleaning is made.		* Since PostgreSQL considers a LO entry as an object on it's own, deleting or upating rows in the user table does'nt delete or delete entries in pg_largeobjects. pg_largeobjects therefore grows infinitely unless a separate cleaning is made.
	* To prevent this, typically a trigger needs to be added which deletes entries in pg_largeobject as descriebd in module 'lo'.		* To prevent this, typically a trigger needs to be added which deletes entries in pg_largeobject as descriebd in module 'lo'.
	* See additional PostgreSQL modules [http://www.postgresql.org/docs/9.1/static/lo.html 'lo'] and [http://www.postgresql.org/docs/9.1/static/vacuumlo.html 'vaccumlo'] in the PostgreSQL docs.		* See additional PostgreSQL modules [http://www.postgresql.org/docs/9.1/static/lo.html 'lo'] and [http://www.postgresql.org/docs/9.1/static/vacuumlo.html 'vaccumlo'] in the PostgreSQL docs.

Stefan: /* Introduction */

2012-01-08T15:19:58Z

Introduction

← Nächstältere Version		Version vom 8. Januar 2012, 15:19 Uhr
Zeile 3:		Zeile 3:
	In [[PostgreSQL]] there exist several ways to manage Binary Large Objects (LOB, BLOB):		In [[PostgreSQL]] there exist several ways to manage Binary Large Objects (LOB, BLOB):
	# Basic binary data type BYTEA		# Basic binary data type BYTEA
	# Large object (LO) facility 'pg_largeobject' ~~(a system table)~~		# Basic character data type TEXT
			# Large object (LO) facility 'pg_largeobject'
	# Data type DATALINK (experimental)		# Data type DATALINK (experimental)
	~~# Basic character data type TEXT~~

	PostgreSQL also supports a storage system called "TOAST" that automatically stores values larger than a single database page (typically 8 KB) into a secondary storage area per table. This defines the size limit of of any column/field to 1 GB.		PostgreSQL also supports a storage system called "TOAST" that automatically stores values larger than a single database page (typically 8 KB) into a secondary storage area per table. This defines the size limit of of any column/field to 1 GB.

	Regarding text ~~type~~ it's just here for completeness. ~~Data type text~~ is a variable character type with unlimited length (up to 1 GB). One can also store encoded binary codes in it.		Regarding data type ''text'' it's just here for completeness. This is a variable character type with unlimited length (up to 1 GB). One can also store encoded binary codes in it.

			Here are some design and implementation questions regarding large objects:
			* Is the LO (conceptually) part of the object/entity - or just associated with it?
			* How is the LO accessed: as encoded string, as file or stream-based?
			* If not stored within the table, who maintains the integrity of the LO?

	== BYTEA ==		== BYTEA ==

Stefan: /* Introduction */

2012-01-08T15:12:34Z

Introduction

← Nächstältere Version		Version vom 8. Januar 2012, 15:12 Uhr
Zeile 2:		Zeile 2:

	In [[PostgreSQL]] there exist several ways to manage Binary Large Objects (LOB, BLOB):		In [[PostgreSQL]] there exist several ways to manage Binary Large Objects (LOB, BLOB):
	# ~~Data~~ type BYTEA		# Basic binary data type BYTEA
	# Large object (LO) facility 'pg_largeobject' (a system table)		# Large object (LO) facility 'pg_largeobject' (a system table)
	# Data type DATALINK (experimental)		# Data type DATALINK (experimental)
	# TEXT ~~type~~		# Basic character data type TEXT

	PostgreSQL also supports a storage system called "TOAST" that automatically stores values larger than a single database page (typically 8 KB) into a secondary storage area per table. This defines the size limit of of any column/field to 1 GB.		PostgreSQL also supports a storage system called "TOAST" that automatically stores values larger than a single database page (typically 8 KB) into a secondary storage area per table. This defines the size limit of of any column/field to 1 GB.